如何让SQL查询按Class返回单行而非两行？

嘿，我来帮你搞定这个问题！你现在遇到的情况是因为你的GROUP BY子句里包含了HHIBCCD字段——这个字段有两种值（'Y'和非'Y'），所以同一个Class会被分成两组，最终返回两行结果。要实现每个Class对应一行数据，我们只需要调整分组逻辑和聚合方式就可以了：

修改后的查询语句 #

--Invoice summary by class
SELECT HHICUSN AS "Account", 
       FFDCNMB AS "Name", 
       HHICLSN AS "Class", 
       FFBSDSC AS "Description",
       SUM(CASE WHEN HHIBCCD = 'Y' THEN HHIQYSA ELSE 0 END) AS "Piece",
       SUM(CASE WHEN HHIBCCD != 'Y' THEN HHIQYSA ELSE 0 END) AS "Case",
       SUM(HHIEXSN) AS "Extended"
FROM S2151BDW.PWRDTA.HHIORDDP
JOIN S2151BDW.PWRDTA.HHHORDHP ON HHHINVN = HHIINVN AND HHHCMPN = HHICMPN
JOIN S2151BDW.PWRDTA.FFDCSTBP ON FFDCUSN = HHHCUSN AND FFDCMPN = HHICMPN
JOIN S2151BDW.PWRDTA.FFBCLSAP ON FFBCLSN = HHICLSN AND FFBCMPN = HHICMPN
WHERE HHICMPN = ' 1' AND
      HHICUSN = ' 960143' AND --10 characters
      HHIDTES BETWEEN '20200101' AND '20201016'
GROUP BY HHICUSN, FFDCNMB, HHICLSN, FFBSDSC -- 移除了HHIBCCD
ORDER BY HHICLSN

关键修改点说明 #

• 移除GROUP BY中的HHIBCCD：这样同一个Class的所有记录会被合并到同一组里，不会再按HHIBCCD的不同值拆分
• 将CASE语句嵌套到SUM内部：直接在聚合函数里判断每条记录的HHIBCCD值，分别汇总出"Piece"和"Case"的总数，而不是先分组再计算

这样调整后，每个Class就只会返回一行汇总数据啦！

内容的提问来源于stack exchange，提问作者Dan B.