我正在使用 Oracle SQL 創建一個資料透視表，其中底部有一個總行。它確實有效，但是該行的第一列顯示為“-”，而我希望它是“總計”這是我當前的代碼：
SELECT
TEAM,
COUNT(CASE WHEN status = 'Idea' THEN 1 END) AS "Idea",
COUNT(CASE WHEN status = 'Published' THEN 1 END) AS "Published",
COUNT(CASE WHEN status = 'Submitted For Publication' THEN 1 END) AS "Submitted For Publication",
COUNT(CASE WHEN status = 'In Progress' THEN 1 END) AS "In Progress",
COUNT(*) AS Total
FROM TEAM_TRACKER
WHERE status IN ('Idea', 'Published', 'Submitted For Publication', 'In Progress')
GROUP BY rollup (TEAM)
我怎樣才能做到這一點？謝謝！
uj5u.com熱心網友回復：
如果分組鍵不可為空，請遵循合并建議。
更安全且專為此目的而設計的就是使用grouping_id那個標識總行
例子
create table team_tracker as
(select '1' team from dual union all
select '1' team from dual union all
select '2' team from dual union all
select null team from dual);
select
case when grouping_id(team) = 1 then 'Total' else team end as team,
count(*) cnt
from team_tracker
group by rollup(team);
TEAM CNT
----- ----------
1 2
2 1
1
Total 4
uj5u.com熱心網友回復：
只需coalesce(Team,'TOTAL')在您的選擇中使用。但這假設每個團隊都不能為空。雖然...這種型別的資料編輯最好在用戶界面中完成，而不是在資料層。
演示
with CTE AS (SELECT 1 as amt, 'a' team from dual UNION ALL
SELECT 1 as amt, 'a' team from dual UNION ALL
SELECT 1 as amt, 'a' team from dual UNION ALL
SELECT 2 as amt, 'b' team from dual UNION ALL
SELECT 3 as amt, 'c' team from dual)
SELECT sum(Amt), coalesce(team,'TOTAL')
FROM CTE
GROUP BY rollup (team)
給我們：
---------- ------------------------
| SUM(AMT) | COALESCE(TEAM,'TOTAL') |
---------- ------------------------
| 3 | a |
| 2 | b |
| 3 | c |
| 8 | TOTAL |
---------- ------------------------
uj5u.com熱心網友回復：
從您上一個問題的答案中，使用GROUPING_ID：
SELECT CASE GROUPING_ID(team)
WHEN 0
THEN team
ELSE 'Total'
END AS team,
COUNT(CASE WHEN status = 'Idea' THEN 1 END) AS "Idea",
COUNT(CASE WHEN status = 'Published' THEN 1 END) AS "Published",
COUNT(CASE WHEN status = 'Submitted For Publication' THEN 1 END) AS "Submitted For Publication",
COUNT(CASE WHEN status = 'In Progress' THEN 1 END) AS "In Progress",
COUNT(*) AS Total
FROM TEAM_TRACKER
WHERE status IN ('Idea', 'Published', 'Submitted For Publication', 'In Progress')
GROUP BY ROLLUP(TEAM)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/448752.html
下一篇：使用特定選擇串列插入SQL