按 NAME 和 YEAR 分組，對 NAME 進行分類

我有一張FINANCE_SOURCE桌子（我想它可能被稱為垂直桌子）：

+-----------+------------+-------------+--------+------+
| SOURCE_ID | PROJECT_ID | SOURCE_NAME | AMOUNT | YEAR |
+-----------+------------+-------------+--------+------+
|       101 |          1 | A           |  10.00 | 2017 |
|       102 |          1 | B           |   5.00 | 2017 |
|       103 |          1 | B           |  15.00 | 2017 |
|       104 |          1 | B           |  70.00 | 2016 |
|       105 |          1 | C           |  30.00 | 2017 |
|       106 |          1 | D           |   1.00 | 2016 |
|       107 |          1 | D           |  20.00 | 2017 |
+-----------+------------+-------------+--------+------+

我想要GROUP BY的SOURCE_NAME，每個都有一個單獨的組YEAR。

我想要任何SOURCE不屬於 A或被B歸類為的記錄OTHER：

+------------+-------------+--------+------+
| PROJECT_ID | SOURCE_NAME | AMOUNT | YEAR |
+------------+-------------+--------+------+
|          1 | A           |  10.00 | 2017 |
|          1 | B           |  20.00 | 2017 |
|          1 | B           |  70.00 | 2016 |
|          1 | OTHER       |  50.00 | 2017 |
|          1 | OTHER       |   1.00 | 2016 |
+------------+-------------+--------+------+

我怎樣才能做到這一點？性能很重要。

將 CASE 表達式添加到 GROUP BY 子句。

SELECT   PROJECT_ID,
        CASE WHEN SOURCE_NAME = 'A' THEN 'A'
             WHEN SOURCE_NAME = 'B' THEN 'B'
             ELSE 'OTHER'
        END SOURCE_NAME,
        SUM(AMOUNT) AMOUNT,
        YEAR
FROM     FINANCE_SOURCE
GROUP BY PROJECT_ID, 
        CASE WHEN SOURCE_NAME = 'A' THEN 'A'
             WHEN SOURCE_NAME = 'B' THEN 'B'
             ELSE 'OTHER'
        END,
        YEAR

引用自：https://dba.stackexchange.com/questions/167527