SQL报表之户籍管理实例

最新推荐文章于 2022-11-12 13:38:38 发布

lilaoshi

最新推荐文章于 2022-11-12 13:38:38 发布

阅读量763

点赞数

分类专栏：数据库文章标签：报表 sql float insert table 语言

本文链接：https://blog.youkuaiyun.com/lilaoshi/article/details/2207035

版权

数据库专栏收录该内容

37 篇文章

订阅专栏

这个示例展示了如何使用SQL创建一个人口管理表格并进行数据统计，包括不同年龄段的男女数量、比例以及女性的已婚比例。通过CASE语句进行条件筛选，使用COUNT、SUM和GROUP BY进行数据聚合，并通过CONVERT和ROUND函数处理结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

　/*统计局户籍管理实例*/
　　create table person
　　(
　　sid int primary key identity(1000,1),
　　age int,
　　sex varchar(10),
　　ismarriged varchar(10)
　　)
　　insert into person select 20,'male','true'
　　union select 31,'female','true'
　　union select 28,'male','false'
　　union select 23,'male','true'
　　union select 34,'female','false'
　　union select 25,'female','false'
　　union select 35,'female','true'
　　union select 24,'male','true'
　　union select 29,'female','true'
　　union select 30,'male','false'
　　union select 31,'female','true'
　　union select 21,'female','true'
　　union select 22,'male','false'
　　union select 32,'female','true'
　　union select 27,'male','true'
　　union select 27,'female','true'
　　union select 33,'female','false'
　　union select 22,'male','false'
　　union select 24,'male','false'
　　union select 25,'male','true'
　　union select 34,'female','false'
　　
　　select * from person
　　
　　/*显示各年龄段人数以及男女比例*/
　　select sex
　　 ,convert(varchar,sum(case when age between 20 and 24 then 1 else 0 end)) as '20-24'
　　 ,convert(varchar,sum(case when age between 25 and 29 then 1 else 0 end)) as '25-29'
　　 ,convert(varchar,sum(case when age between 30 and 34 then 1 else 0 end)) as '30-34'
　　 ,convert(varchar,count(1)) as '总数'
　　from person
　　group by sex
　　union all
　　select '男女比例'
　　 ,convert(varchar,round(((select count(1) from person where sex='male' and (age between 20 and 24))+0.0)
　　 /(select count(1) from person where sex='female' and (age between 20 and 24)),2))
　　 ,convert(varchar,round(((select count(1) from person where sex='male' and (age between 25 and 29))+0.0)
　　 /(select count(1) from person where sex='female' and (age between 25 and 29)),2))
　　 ,convert(varchar,round(((select count(1) from person where sex='male' and (age between 30 and 34))+0.0)
　　 /(select count(1) from person where sex='female' and (age between 30 and 34)),2))
　　 ,convert(varchar,round(((select count(1) from person where sex='male')+0.0)
　　 /(select count(1) from person where sex='female'),2))
　　--起别名用数字及特殊字符加单引号
　　--＋0.0时可以将整形换为浮点，类似于C语言float和int运算时会转为float
　　--如果不用convert转换为字符行，结果集将把union上面的人数也显示为浮点，不知道为什么，大概union上下的东西数据类型必须一样吧，所以只好转换为varchar
　　--关于比例应该如何显示，可以用小数，百分比，（此题用小数，下题用百分比）感觉此题最好的是只在中间加个‘：’或‘/’但是如何约分是个问题，哪个高手解决一下
　　--round函数居然不起作用！哪个牛人告诉我为啥？
　　
　　
　　
　　/*显示各年龄段女性人数及已婚比例*/
　　select
　　 convert(varchar,sum(case when age between 20 and 24 then 1 else 0 end)) as '20-24'
　　 ,convert(varchar,sum(case when age between 25 and 29 then 1 else 0 end)) as '25-29'
　　 ,convert(varchar,sum(case when age between 30 and 34 then 1 else 0 end)) as '30-34'
　　 ,convert(varchar,count(1)) as '总数'
　　from person where sex='female'
　　group by sex
　　union all
　　select
　　 convert(varchar,round(((select count(1) from person where sex='female' and (age between 20 and 24) and ismarriged='true')+0.0)
　　 /(select count(1) from person where sex='female' and (age between 20 and 24))*100,2))+'%'
　　 ,convert(varchar,round(((select count(1) from person where sex='female' and (age between 25 and 29) and ismarriged='true')+0.0)
　　 /(select count(1) from person where sex='female' and (age between 25 and 29))*100,2))+'%'
　　 ,convert(varchar,round(((select count(1) from person where sex='female' and (age between 30 and 34) and ismarriged='true')+0.0)
　　 /(select count(1) from person where sex='female' and (age between 30 and 34))*100,2))+'%'
　　 ,convert(varchar,round(((select count(1) from person where sex='female' and ismarriged='true')+0.0)
　　 /(select count(1) from person where sex='female')*100,2))+'%'
　　--嘶喊！round函数为啥不起作用？