带有 subQ 的 MYSQL 查询

Question

我正在尝试获取列的所有总和，因为我正在这样做，我想加入已发货的文件，因此我只获取当前尚未发货的订单的总和。通过我的努力，我不断得到一个结果，其中所有列的计算金额都是错误的。预先感谢您。

我相信我需要写一个子问题，但我在尝试实现这一点时遇到了问题。如果我删除连接，结果是完美的，没有问题，但我需要连接，所以我只计算未发货的元素。我相信它从连接表中提取了一些其他记录。预先感谢您。

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，我还是一个初学者。谢谢。

Answer 1

当您将普通列与聚合函数(如 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