mysql - 带subQ的MYSQL查询

我正在尝试获取列的所有SUMS，因为我要加入已提交的文件，所以我仅可以获取尚未交付的当前订单的SUMS。从我的努力中，我不断得到一个结果，其中所有列的计算量都是错误的。先感谢您。

我相信我需要写一个subQ，但是在到达那里时遇到了问题。
如果删除JOIN，则结果是完美的，没有问题，但是我需要联接，所以我只计算未发货的项目。我相信它会从联接表中提取其他一些记录。先感谢您。

SELECT XX.order_num, XX.shipped, PP.order_num AS JON, PP.part_num AS JPT, SUM(PP.total_qty) AS QTY, SUM(PP.work_time) AS WT,SUM(PP.setup_time) AS ST,SUM(PP.scrap) AS SC
  FROM PP
    JOIN XX
    ON XX.order_num = PP.order_num
  WHERE PP.department='RIBBON'
  AND PP.ribbon_type='CRIMPING' AND XX.shipped IS NULL
  GROUP BY part_num
  ORDER BY PP.order_num DESC

我得到这个：

so185702    6609628 8,120   92.67 HRS   1.92 HRS    0

什么时候应该阅读以下内容：

so185702        6609628   760      545            15            0

我只需要编写subQ的帮助，我还是一个初学者。谢谢。

最佳答案

当将普通列与聚合函数（例如sum）混合使用时，需要GROUP BY子句，在其中列出SELECT中所有不具有聚合函数的列。在查询的前四列中：

SELECT
  XX.order_num,
  XX.shipped,
  PP.order_num AS JON,
  PP.part_num AS JPT,
  SUM(PP.total_qty) AS QTY,
  SUM(PP.work_time) AS WT,
  SUM(PP.setup_time) AS ST,
  SUM(PP.scrap) AS SC
FROM PP
  JOIN XX ON XX.order_num = PP.order_num
WHERE PP.department='RIBBON' AND PP.ribbon_type='CRIMPING' AND XX.shipped IS NULL
GROUP BY XX.order_num, XX.shipped, PP.order_num, PP.part_num
ORDER BY PP.order_num DESC

关于mysql - 带subQ的MYSQL查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57152421/

subq

mysql - 带subQ的MYSQL查询