我有一些這樣的資料：
| 型別 | 一個 | 乙 | C |
|---|---|---|---|
| 啊啊啊 | 5 | 6 | 2022-05-01 |
| 啊啊啊 | 8 | 7 | 2022-05-08 |
| 啊啊啊 | 9 | 8 | 2022-05-16 |
| bbb | 7 | 4 | 2022-05-09 |
| bbb | 6 | 8 | 2022-05-14 |
| bbb | 3 | 3 | 2022-05-25 |
我需要得到這樣的輸出：
| 型別 | 一個 | D | C |
|---|---|---|---|
| 啊啊啊 | 22 | 8 | 2022-05-16 |
| bbb | 16 | 3 | 2022-05-25 |
我當前的代碼：
SELECT type, SUM(A) AS A, SUM(D) AS D, MAX(C) AS C
FROM
(SELECT
type, A, B, C,
CASE
WHEN C = MAX(C) OVER(PARTITION BY type)
THEN B
ELSE 0
END AS D,
FROM
data) AS bbb
GROUP BY
type
有沒有更好的方法不使用子查詢？
我正在使用 SQL Server。
uj5u.com熱心網友回復：
由于您要求的查詢沒有子查詢，您可以試試這個：
SELECT DISTINCT TYPE,
SUM(A) OVER(PARTITION BY TYPE) AS A,
FIRST_VALUE(B) OVER(PARTITION BY TYPE ORDER BY C DESC) AS D,
MAX(C) OVER(PARTITION BY TYPE) AS C
FROM data;
輸出：
| 型別 | 一個 | D | C |
|---|---|---|---|
| 啊啊啊 | 22 | 8 | 2022-05-16 00:00:00.000 |
| bbb | 16 | 3 | 2022-05-25 00:00:00.000 |
請參閱此db<>fiddle演示。
uj5u.com熱心網友回復：
這是一種ROW_NUMBER與旋轉邏輯一起使用的方法：
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY C DESC) rn
FROM data
)
SELECT TYPE, SUM(A) AS A,
MAX(CASE WHEN rn = 1 THEN B END) AS D,
MAX(CASE WHEN rn = 1 THEN C END) AS C
FROM cte
GROUP BY TYPE;
這是一個作業演示。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/494083.html
上一篇：Postgresql截斷文本列