时间戳

首页 > 解决方案 > 带有 subQ 的 MYSQL 查询

mysql - 带有 subQ 的 MYSQL 查询

问题描述

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

我相信我需要写一个 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 的帮助，我还是个初学者。谢谢你。

标签： mysql

解决方案

当您将普通列与聚合函数（如 sum）混合时，您需要GROUP BY-clause 列出所有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

推荐阅读