這是預期的輸出。
ID | GRAND_TOTAL | CHANNEL_1 | CHANNEL_2
1 | 100.00 | 50.00 | 50.00
2 | 500.00 | 100.00 | 400.00
但我的查詢結果如下：
ID | GRAND_TOTAL | CHANNEL_1 | CHANNEL_2
1 | 100.00 | 50.00 | 0
2 | 100.00 | 0 | 50.00
1 | 500.00 | 100.00 | 0
2 | 500.00 | 0 | 400.00
這是我在查詢中嘗試的。
SELECT
ID,
SUM(Amount) AS GRAND_TOTAL,
CASE WHEN CHANNEL_ID = 1 THEN AMOUNT ELSE 0 END CHANNEL_1,
CASE WHEN CHANNEL_ID = 2 THEN AMOUNT ELSE 0 END CHANNEL_2
FROM
CHANNEL_AMOUNT
GROUP BY
ID, CHANNEL_ID, AMOUNT
uj5u.com熱心網友回復：
GROUP BY ____意思是“我希望每個____有一個結果行”。您不希望每個 ID、渠道和金額有一個結果行，但每個 ID 只有一個結果行。因此：GROUP BY id。然后你想要每個通道的總和SUM，所以使用.
SELECT
id,
SUM(amount) AS grand_total,
SUM(CASE WHEN channel_id = 1 THEN amount ELSE 0 END) AS channel_1,
SUM(CASE WHEN channel_id = 2 THEN amount ELSE 0 END) AS channel_2
FROM channel_amount
GROUP BY id
ORDER BY id;
（在標準 SQL 中，這應該是SUM(amount) FILTER (WHERE channel_id = 1)，但 SQL Server 還沒有過濾子句，所以我們在聚合函式中使用 case 運算式。）
uj5u.com熱心網友回復：
看起來您只想按 Id 分組
SELECT
ID,
SUM(Amount) AS GRAND_TOTAL,
SUM(CASE WHEN CHANNEL_ID = 1 THEN AMOUNT ELSE 0 END) CHANNEL_1,
SUM(CASE WHEN CHANNEL_ID = 2 THEN AMOUNT ELSE 0 END) CHANNEL_2
FROM
CHANNEL_AMOUNT
GROUP BY
ID
uj5u.com熱心網友回復：
你可以試試 GROUP BY ID。
SELECT
ID,
SUM(Amount) AS GRAND_TOTAL,
CASE WHEN CHANNEL_ID = 1 THEN AMOUNT ELSE 0 END CHANNEL_1,
CASE WHEN CHANNEL_ID = 2 THEN AMOUNT ELSE 0 END CHANNEL_2
FROM
CHANNEL_AMOUNT
GROUP BY
ID
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/507048.html
上一篇：使用sp_add_jobschedule調度SQL作業
下一篇：基于滯后/領先的分組