我想要一個類似于下表的輸出，我可以在其中使用 Case 運算式從查詢中獲取計數和百分比。表中的數字僅用于說明目的。
| 年齡階層 | 數數 | 百分比 |
|---|---|---|
| 沉默的 | 1 | 10% |
| 嬰兒潮一代 | 2 | 20% |
| 一代 | 1 | 10% |
| 千禧一代 | 4 | 40% |
| Z世代 | 2 | 20% |
我運行了以下查詢：
SELECT
COUNT(CASE
WHEN birth_year between 1928 and 1945 THEN 'Silent'
WHEN birth_year between 1946 and 1964 THEN 'Boomers'
WHEN birth_year between 1965 and 1980 THEN 'Generation X'
WHEN birth_year between 1981 and 1996 THEN 'Millennial'
WHEN birth_year between 1997 and 2012 THEN 'Generation Z'
ELSE 'Other'
END) as 'AgeGroup'
FROM CombinedTable
輸出給我的總數不是我想要的。
注意： 我使用的是 Microsoft SQL Server 2017
uj5u.com熱心網友回復：
您需要按該CASE運算式進行分組。您可以將其放在 a 中CROSS APPLY以避免重復。
VALUES回傳一個虛擬表，如果你使用CROSS APPLY你可以將它連接到每一行，同時能夠從以前參考的表中插入值
SELECT
v.AgeGroup,
COUNT(*) AS Count,
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS Percentage
FROM CombinedTable ct
CROSS APPLY (VALUES (
CASE
WHEN birth_year between 1928 and 1945 THEN 'Silent'
WHEN birth_year between 1946 and 1964 THEN 'Boomers'
WHEN birth_year between 1965 and 1980 THEN 'Generation X'
WHEN birth_year between 1981 and 1996 THEN 'Millennial'
WHEN birth_year between 1997 and 2012 THEN 'Generation Z'
ELSE 'Other'
END
)) v(AgeGroup)
GROUP BY
v.AgeGroup;
這也更容易通過連接完成
在這種情況下，我們構建了一個可能選項的虛擬表，并以正常方式將其連接到每一行
SELECT
ISNULL(v.AgeGroup, 'Other') AS AgeGroup,
COUNT(*) AS Count,
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS Percentage
FROM CombinedTable ct
LEFT JOIN (VALUES
(1928,1945,'Silent'),
(1946,1964,'Boomers'),
(1965,1980,'Generation X'),
(1981,1996,'Millennial'),
(1997,2012,'Generation Z')
) v(StartYear, EndYear, AgeGroup) ON tc.birth_year BETWEEN v.StartYear AND v.EndYear
GROUP BY
ISNULL(v.AgeGroup, 'Other');
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/336025.html
標籤：sql sql-server 查询语句
上一篇：在子查詢中聯合所有