Select p.prodCode,
       p.description,
       p.unit,
       SUM(sd.quantity) "Total quantity"
FROM salesDetail sd
RIGHT JOIN product p
    ON p.prodCode = sd.prodCode
GROUP BY p.prodCode
ORDER BY 4 DESC

帮帮我!我的脚本未运行。我需要获取每种产品的总量,但是我的分组依据却无效。

最佳答案

在单独的子查询中计算每种产品的数量总和,然后将其返回到原始product表:

SELECT t1.prodCode,
       t1.description,
       t1.unit,
       t2.total_quantity
FROM product t1
INNER JOIN
(
    SELECT p.prodCode, SUM(sd.quantity) total_quantity
    FROM product p
    LEFT JOIN salesDetail sd
        ON p.prodCode = sd.prodCode
    GROUP BY p.prodCode
) t2
    ON t1.prodCode = t2.prodCode

请注意,通过切换子查询中联接表的顺序,我用RIGHT JOIN替换了LEFT JOIN

更新:

如果您绝对需要使用RIGHT JOIN,则只需将子查询替换为:
SELECT p.prodCode, SUM(sd.quantity) total_quantity
FROM salesDetail sd
RIGHT JOIN product p
    ON p.prodCode = sd.prodCode
GROUP BY p.prodCode

关于group-by - DB2中的分组依据和正确联接,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/41679077/

10-09 00:30