需求：需统计出某个集合内，某个段所占的比例，涉及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 (
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岁’
WHEN 年龄>60 THEN ’60岁以上’
ELSE ‘年龄错误’ 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 年龄段

版权声明：本文为since-1995原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/since-1995/p/14469718.html