按指定返回所有捐赠者的最高 SUM 值

Question

我有以下脚本：

SELECT DISTINCT GIFT_ID, GIFT_DESG, SUM(GIFT_AMT)
FROM GIFT_TABLE
GROUP BY GIFT_ID, GIFT_DESG

它将返回如下内容：

GIFT_ID         GIFT_DESG      SUM(GIFT_AMT)
1                A               25
1                B              500
1                C               75
2                A              100
2                B              200
2                C              300
...

我想要的结果是：

GIFT_ID         GIFT_DESG      SUM(GIFT_AMT)
1                B           500
2                C           300

我该怎么做？

可能是row_number((吧？我认为这是按指定对礼物金额求和的东西，这让我很失望。

谢谢。

Answer 1

如果你的DBMS支持ROW_NUMBER窗口函数，你可以尝试按GIFT_ID顺序SUM(GIFT_AMT)然后获取行rn = 1行。

SELECT t1.GIFT_ID,t1.GIFT_DESG,t1.GIFT_AMT 
FROM (
SELECT t1.*,ROW_NUMBER() OVER(PARTITION BY GIFT_ID ORDER BY GIFT_AMT DESC) rn
FROM (
SELECT  GIFT_ID, GIFT_DESG, SUM(GIFT_AMT) GIFT_AMT
FROM GIFT_TABLE
GROUP BY GIFT_ID, GIFT_DESG
) t1
) t1
where rn =1

注意

您已经使用了GROUP BYDISTINCT关键字没有意义，您可以将其从查询中删除。

---

这是一个示例

CREATE TABLE T(
GIFT_ID int,
GIFT_DESG varchar(5),
GIFT_AMT int
);

insert into t values (1,'A' ,25);
insert into t values (1,'B' ,500);
insert into t values (1,'C' ,75);
insert into t values (2,'A' ,100);
insert into t values (2,'B' ,200);
insert into t values (2,'C' ,300);

查询 1：

SELECT t1.GIFT_ID,t1.GIFT_DESG,t1.GIFT_AMT 
FROM (
SELECT t1.*,ROW_NUMBER() OVER(PARTITION BY GIFT_ID ORDER BY GIFT_AMT DESC) rn
FROM T t1
) t1
where rn =1

结果：

| GIFT_ID | GIFT_DESG | GIFT_AMT |
|---------|-----------|----------|
|       1 |         B |      500 |
|       2 |         C |      300 |

Answer 2

您可以在没有子查询的情况下执行此操作：

SELECT TOP (1) WITH TIES GIFT_ID, GIFT_DESG, SUM(GIFT_AMT)
FROM GIFT_TABLE
GROUP BY GIFT_ID, GIFT_DESG
ORDER BY ROW_NUMBER() OVER (PARTITION BY GIFT_ID ORDER BY SUM(GIFT_AMT) DESC);

Answer 3

你也可以这样做

WITH t as
SELECT GIFT_ID, GIFT_DESG, SUM(GIFT_AMT) AS GIFT_AMT
FROM GIFT_TABLE
GROUP BY GIFT_ID, GIFT_DESG)
SELECT  GIFT_ID, 
max(GIFT_DESG) KEEP (DENSE_RANK LAST ORDER BY GIFT_AMT), 
max(GIFT_AMT) GIFT_AMT
FROM T
GROUP BY GIFT_ID;