使用SQL Server語句統計某年齡段人數占總人數的比例（多層查詢語句嵌套-比例分析）

需求：需統計出某個集合内，某個段所占的比例，涉及SELECT查詢語句的嵌套，如有疑問可留言。

如下：

--按性别進行年度挂号年齡段分析

--男

SELECT 年齡段,SUM(人數) 數量,cast(cast((SUM(人數)/((select count(*) from YXHIS2020..VTBMZGHMX2020 WHERE BTH=0 AND CXB='男')*1.0)*100) as decimal(9,2)) as varchar)+'%' 所占比例 FROM (

SELECT

CASE

WHEN 年齡 between 0 and 12 THEN '0-12 歲'

WHEN 年齡 between 13 and 24 THEN '13-24歲'

WHEN 年齡 between 25 and 36 THEN '25-36歲'

WHEN 年齡 between 37 and 48 THEN '37-48歲'

WHEN 年齡 between 49 and 60 THEN '49-60歲'

ELSE '60歲以上' END 年齡段, 人數,性别

FROM (

SELECT 年齡,人數,性别 FROM (

select DATEDIFF(year, DCSNY, DGH) 年齡,count(CNL) 人數,CXB 性别

from YXHIS2020..VTBMZGHMX2020 WHERE BTH=0 AND CXB='男' group by DATEDIFF(year, DCSNY, DGH),CXB )A GROUP BY A.年齡,A.人數,A.性别)A )B GROUP BY 年齡段

--女

SELECT 年齡段,SUM(人數) 數量,cast(cast((SUM(人數)/((select count(*) from YXHIS2020..VTBMZGHMX2020 WHERE BTH=0 AND CXB='女')*1.0)*100) as decimal(9,2)) as varchar)+'%' 所占比例 FROM (

WHEN 年齡>60 THEN '60歲以上'

ELSE '年齡錯誤' END 年齡段, 人數,性别

from YXHIS2020..VTBMZGHMX2020 WHERE BTH=0 AND CXB='女' group by DATEDIFF(year, DCSNY, DGH),CXB )A GROUP BY A.年齡,A.人數,A.性别)A )B GROUP BY 年齡段