透視、分類和分組依據

這個問題與我在這裡遇到的另一個 DBA SE 問題相似但又不同：Group by NAME and YEAR, categorize 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 |
+-----------+------------+-------------+--------+------+

我想重新排列數據，以便SOURCE_A,SOURCE_B和OTHER（任何不是A或的B）位於單獨的列中。

數據需要分組為單獨的YEARS：

+------------+---------+---------+-------------+-------+------+
| PROJECT_ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | TOTAL | YEAR |
+------------+---------+---------+-------------+-------+------+
|          1 |      10 |      20 |          50 |    80 | 2017 |
|          1 |         |      70 |           1 |    71 | 2016 |
+------------+---------+---------+-------------+-------+------+

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

您可以使用 SUM(CASE…) 語句來獲取它。

SELECT   PROJECT_ID,
         COALESCE(SUM(CASE WHEN SOURCE_NAME = 'A' THEN AMOUNT END), 0) A_TOTAL,
         COALESCE(SUM(CASE WHEN SOURCE_NAME = 'B' THEN AMOUNT END), 0) B_TOTAL,
         COALESCE(SUM(CASE WHEN SOURCE_NAME NOT IN ('A','B') THEN AMOUNT END), 0) OTHER_TOTAL,
         COALESCE(SUM(AMOUNT), 0) AS TOTAL,
         YEAR
FROM     FINANCE_SOURCE
GROUP BY PROJECT_ID, YEAR;

項目 ID | A_TOTAL | B_TOTAL | OTHER_TOTAL | 總計 | 年
---------: | ------: | ------: | ----------: | ----: | ---:
 1 | 0 | 70 | 1 | 71 | 2016
 1 | 10 | 20 | 50 | 80 | 2017

dbfiddle在這裡

正如@JackDouglas 所建議的那樣，讓我添加他的新解決方案，它可能會稍微快一些。

SELECT PROJECT_ID
     , A_TOTAL
     , B_TOTAL
     , nvl(TOTAL,0)-nvl(A_TOTAL,0)-nvl(B_TOTAL,0) OTHER_TOTAL
     , TOTAL
     , YEAR
FROM( SELECT   PROJECT_ID,
               COALESCE(SUM(CASE WHEN SOURCE_NAME = 'A' THEN AMOUNT END), 0) A_TOTAL,
               COALESCE(SUM(CASE WHEN SOURCE_NAME = 'B' THEN AMOUNT END), 0) B_TOTAL,
               COALESCE(SUM(AMOUNT), 0) AS TOTAL,
               YEAR
      FROM     FINANCE_SOURCE
      GROUP BY PROJECT_ID, YEAR );

dbfiddle here

（包括執行計劃。）

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