SQL选择和的最大值

Question

我是论坛和SQL的新手，非常感谢所有的信息。我找不到一个适合我的解决方案，所以我想在这里试一试。在雪花中运作。我的数据集看起来是这样的(对于麻烦的格式，很抱歉)：

PO  | DIV | PROD |  QTY |   CUST
123 | 1  | x |  10 |    Sonic
234 |   1 | x   | 9 |   Sonic
345 | 1 |   x | 8   | McD
456 | 1 | x | 10    | Wendy's

我想通过DIV、PROD和CUST对数量进行求和。一旦我得到了这些求和的数量，我想通过DIV和PROD获得最大的和(QTY)，但保留CUST字段。因此，上面的答案将如下所示：

1 | x | 19| Sonic

只需注意，我显然有一个更大的数据库，所以我将使用max(sum())列出所有唯一的乘积/除法组合，大约600K行。

这里的代码让我获得了sum(QTY)，但现在我需要在保留CUST字段的同时拉出具有最大sum(QTY)的行。你能帮上忙吗？我找到了一些关于每个组的greatest-n-per-group的信息，但我不确定这是不是应该使用以及如何使用

SELECT DIV, PROD, CUST, SUM(QTY) as QTY
from table
GROUP BY 
    DIV,
    PROD,
    CUST
Order by 
    DIV,
    PROD

编辑:Jay的CTE方法对我很有效，但我忘了提一下，我想用它创建一个表。创建或替换表不能与CTE一起使用。有没有使用CTE方法或其他方法创建表的方法？

谢谢!

Answer 1

MYSQL

SELECT `DIV`, PROD, CUST,QTY from 
(
SELECT `DIV`, PROD, CUST, SUM(QTY) as QTY from 
table1 
GROUP BY `DIV`, PROD, CUST Order by `DIV`, PROD
) AS T
WHERE QTY=(SELECT MAX(QTY) FROM (SELECT `DIV`, PROD, CUST, SUM(QTY) as QTY from 
table1 
GROUP BY `DIV`, PROD, CUST Order by `DIV`, PROD) AS T)

或

SELECT `DIV`, PROD, CUST,QTY from 
(
SELECT `DIV`, PROD, CUST, SUM(QTY) as QTY from 
table1 
GROUP BY `DIV`, PROD, CUST Order by `DIV`, PROD
) AS T ORDER BY QTY DESC LIMIT 1;

实时演示

http://sqlfiddle.com/#!9/f945c2b/19

SQL SERVER

使用CTE

WITH CTE AS
(
 SELECT DIV, PROD, CUST, SUM(QTY) as QTY,
 DENSE_RANK() OVER (PARTITION BY DIV, PROD ORDER BY SUM(QTY) DESC) AS Rank
 FROM 
 table1 
 GROUP BY DIV, PROD, CUST
)
SELECT DIV, PROD, CUST,QTY FROM CTE
WHERE Rank=1
ORDER BY DIV, PROD

使用嵌套查询

SELECT DIV, 
       PROD, 
       CUST,
       QTY 
FROM ( 
      SELECT DIV, PROD, CUST, SUM(QTY) as QTY, 
      DENSE_RANK() OVER (PARTITION BY DIV, PROD ORDER BY SUM(QTY) DESC) AS Rank
      FROM table1 
      GROUP BY DIV, PROD, CUST )AS T1 
 WHERE Rank=1 ORDER BY DIV, PROD;

实时演示

http://sqlfiddle.com/#!18/22001/11

Answer 2

您可以通过having子句过滤聚合sum(qty)

select 
       DIV, PROD, CUST, SUM(QTY) as QTY 
from table
group by DIV, PROD, CUST
having sum(QTY) = ( select max(QTY) from (
                    select sum(QTY) as QTY from table
                    group by DIV, PROD, CUST)a)

Answer 3

在MySQL中尝试：

SELECT A.`DIV`, A.PROD, IF(A.`DIV`=0,'NAN',A.AGG_QTY/A.`DIV`) QTY, A.CUST
FROM (SELECT `DIV`, PROD, CUST, SUM(QTY) AGG_QTY
      FROM `TABLE` 
      GROUP BY `DIV`, PROD, CUST
      ORDER BY SUM(QTY) DESC
      LIMIT 1) A;

查看它在SQL Fiddle上的运行