我有一些類似的選擇，我想加入并匯總值。我已經嘗試過 join、inner join、union、union all，但沒有得到預期的結果。看到選擇非常相似。區別是：
SUM(comumn_02)*.05 AS SUM_01不同于SUM(comumn_02)*.45 AS SUM_02
和第二次選擇AND COLUMN_05 = ALFA不同AND COLUMN_05 = BETA。
整件事都在這里：
SELECT
COLUMN_01,
SUM(COLUMN_02)*.05 AS SUM_01
FROM table
WHERE
COLUMN_03 = 1
AND COLUMN_04 = 2
AND COLUMN_05 = ALFA
GROUP BY COLUMN_01
UNION ALL
SELECT
COLUMN_01,
SUM(COLUMN_02)*.45 AS SUM_02
FROM table
WHERE
COLUMN_03 = 1
AND COLUMN_04 = 2
AND COLUMN_05 = BETA
GROUP BY COLUMN_01;
在此示例中，我們有以下結果：
|-----------|--------|
| COLUMN_01 | SUM_01 |
|-----------|--------|
| value_01 | 465 |
| value_02 | 186 |
| value_03 | 245 |
| value_01 | 102 |
| value_02 | 108 |
| value_03 | 325 |
|--------------------|
但我想要的是：
|-----------|--------|
| COLUMN_01 | SUM_01 |
|-----------|--------|
| value_01 | 567 | //sum of 465 102
| value_02 | 294 | //sum of 186 108
| value_03 | 570 | //sum of 245 570
|--------------------|
最重要的是，這是否可以擴展？即與三個或更多選擇“工會”一起使用它，否則性能會下降很多？
uj5u.com熱心網友回復：
您的查詢中的問題是您正在聚合單個表，然后您正在使用UNION ALL它來獲取您的值。為了解決您的問題，您需要顛倒這兩個操作的順序：
- 首先你應用這個
UNION功能 - 然后你用
SUM聚合函式聚合
WITH CTE AS(
SELECT COLUMN_01,
COLUMN_02*.05 AS COLUMN
FROM table
WHERE COLUMN_03 = 1
AND COLUMN_04 = 2
AND COLUMN_05 = ALFA
UNION ALL
SELECT COLUMN_01,
COLUMN_02*.45 AS COLUMN
FROM table
WHERE COLUMN_03 = 1
AND COLUMN_04 = 2
AND COLUMN_05 = BETA
)
SELECT COLUMN_01,
SUM(COLUMN) AS SUM_01
FROM CTE
GROUP BY COLUMN_01
uj5u.com熱心網友回復：
使用條件聚合：
SELECT COLUMN_01,
SUM(COLUMN_02 * CASE COLUMN_05 WHEN 'ALFA' THEN 0.05 WHEN 'BETA' THEN 0.45 END) AS SUM_01
FROM tablename
WHERE COLUMN_03 = 1 AND COLUMN_04 = 2 AND (COLUMN_05 IN ('ALFA', 'BETA'))
GROUP BY COLUMN_01;
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/493851.html