UC知道年龄分段统计的问题
来源：百度知道 编辑：UC知道 时间：2024/05/09 11:49:37
年龄分段统计的问题
有一个表中有 出生年月日的日期字段，按每10岁分段统计人数SQL如何写？
年龄段 1-10 11-20 21-30 31- 40 41-50 51-60 .....
人数 ？ ？
我找到了一种方法.大家看看哪个更好?SELECT SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 16 AND 20 THEN 1 ELSE 0 END) AS '16-20', SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS '21-30', SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS '31-40', SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS '41-50',SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS '51-60', SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 61 AND 70 THEN 1 ELSE 0 END) AS '61-70' FROM 员工信息表
有一个表中有 出生年月日的日期字段，按每10岁分段统计人数SQL如何写？
年龄段 1-10 11-20 21-30 31- 40 41-50 51-60 .....
人数 ？ ？
我找到了一种方法.大家看看哪个更好?SELECT SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 16 AND 20 THEN 1 ELSE 0 END) AS '16-20', SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS '21-30', SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS '31-40', SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 41 AND 50 THEN 1 ELSE 0 END) AS '41-50',SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 51 AND 60 THEN 1 ELSE 0 END) AS '51-60', SUM(CASE WHEN datediff(year, 出生日期阴, getdate()) BETWEEN 61 AND 70 THEN 1 ELSE 0 END) AS '61-70' FROM 员工信息表
select cast(f1*10+1 as varchar(3))+'-'+cast(f1*10+10 as varchar(3)) as 年龄段,f2 as 人数
from
(select datediff(d,出生日期,getdate())/365/10 as f1,count(*) as f2 from 表名 group by datediff(d,birth_day,getdate())/365/10) a
order by cast(f1*10+1 as varchar(3))+'-'+cast(f1*10+10 as varchar(3))
年龄段的控制你可以在动态网页提交面上直接限制住'select'标签的下拉列表里只显示你要的年龄段存入数据库的时候就不会有这样的问题了。
在SQLserver中年月日是有datatime标签直接写入 插入的方法就是
create table student
(sno char(6),
sage datatime
)
insert into student values('950001','1987-05-10')