我有一张类似于下面的表：
lot | defect | quantity
-------+----------+-----------
lot1 | c | 7
lot1 | c | 2
lot3 | e | 5
lot3 | b | 9
lot3 | a | 5
lot2 | d | 4
lot4 | c | 12
... | ... | ...
我想对行之间的批次和缺陷相等的数量求和，然后按数量总和订购批次（批次 3=9+5+5=19，批次 4=12，批次 1=7+2=9，批次 2=4），然后是数量（每批次内），然后是缺陷。
所以它应该产生以下结果：
lot | defect | SUM(quantity)
-------+----------+----------------
lot3 | b | 9
lot3 | a | 5
lot3 | e | 5
lot4 | c | 12
lot1 | c | 9
lot2 | d | 4
... | ... | ...
我能想到的最接近的是以下查询：
SELECT lot, defect, SUM(quantity)
FROM table
GROUP BY lot, defect
ORDER BY SUM(quantity), lot, defect
结果如下：
lot | defect | SUM(quantity)
-------+----------+----------------
lot4 | c | 12
lot1 | c | 9
lot3 | b | 9
lot3 | a | 5
lot3 | e | 5
lot2 | d | 4
... | ... | ...
您的问题似乎是关于对结果进行排序。解决方案是使用窗口函数ORDER BY:
SELECT lot, defect, SUM(quantity)
FROM table
GROUP BY lot, defect
ORDER BY SUM(SUM(quantity)) OVER (PARTITION BY lot) DESC,
lot, SUM(quantity) DESC, defect;
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
