根据年龄段分组统计超难问题
有这样一个表如下字段
姓名 性别 出生日期
求如下结果根据一个变量来指定间距统计年龄如3
年龄段 合计 男 女
0~3 10 4 6
3~6 6 3 3
6~9 7 5 2
间距年龄可以随意指定如下 5
年龄段 合计 男 女
0~5 10 4 6
5~10 6 3 3
10~15 7 5 2
求一个高效快速的算法，不胜感激
[解决办法]
declare @t table (姓名 varchar(1),性别 varchar(2),出生日期 datetime)insert into @tselect 'a','男','2010-02-01' union allselect 'b','男','2009-02-01' union allselect 'c','女','2008-02-01' union allselect 'd','女','2007-02-01' union allselect 'e','男','2006-02-01' union allselect 'f','男','2007-08-01' union allselect 'g','女','2008-03-01' union allselect 'h','女','2009-01-01'declare @i int set @i=3select sum(case when datediff([year],出生日期,getdate()) between 0 and @i then 1 else 0 end) as 合计,sum(case when datediff([year],出生日期,getdate()) between 0 and @i and 性别='男' then 1 else 0 end) as 男,sum(case when datediff([year],出生日期,getdate()) between 0 and @i and 性别='女' then 1 else 0 end) as 女 from @t union allselect sum(case when datediff([year],出生日期,getdate()) between @i+1 and 2*@i then 1 else 0 end),sum(case when datediff([year],出生日期,getdate()) between @i+1 and 2*@i and 性别='男' then 1 else 0 end) as 男,sum(case when datediff([year],出生日期,getdate()) between @i+1 and 2*@i and 性别='女' then 1 else 0 end) as 女from @tunion allselect sum(case when datediff([year],出生日期,getdate()) between 2*@i+1 and 3*@i then 1 else 0 end),sum(case when datediff([year],出生日期,getdate()) between 2*@i+1 and 3*@i and 性别='男' then 1 else 0 end) as 男,sum(case when datediff([year],出生日期,getdate()) between 2*@i+1 and 3*@i and 性别='女' then 1 else 0 end) as 女from @t/*合计 男 女----------- ----------- -----------5 2 33 2 10 0 0*/
[解决办法]
declare @i int;set @i = 5;select a.d,sum(isnull(b.total,0)) as total,sum(isnull(b.b,0)) as b,sum(isnull(b.w,0)) as wfrom(select (number-1)*@i as mi,number*@i as ma,rtrim((number-1)*@i)+'~'+rtrim(number*@i) as d from master.dbo.spt_values where type='p' where number>0) as a left join (select datediff(year,birthday,getdate()) as age,count(*) as total, b=count(case when sex=1 then 1 end),w=count(case when sex=0 then 1 else 0 end) from tb group by datediff(year,birthday,getdate())) as bon b.age >= a.mi and b.age < b.magroup by a.dhaving sum(isnull(b.total,0))=0
[解决办法]
declare @t table(name sysname,sex bit,birthday datetime)insert @t select 'A',1,'1988-01-02'insert @t select 'B',1,'1986-05-23'insert @t select 'C',0,'1997-01-12'insert @t select 'D',0,'1988-01-02'insert @t select 'E',1,'1968-01-02'insert @t select 'F',1,'1978-05-11'insert @t select 'G',0,'1966-01-02'insert @t select 'H',1,'1989-01-02'insert @t select 'I',1,'1990-01-02'insert @t select 'J',0,'1992-01-02'insert @t select 'K',0,'1987-01-02'insert @t select 'W',1,'1986-01-02'insert @t select 'Q',0,'1985-01-02'declare @i intset @i = 9select a.d,sum(isnull(b.total,0)) as total,sum(isnull(b.b,0)) as b,sum(isnull(b.w,0)) as wfrom(select (number-1)*@i as mi,number*@i as ma,rtrim((number-1)*@i)+'~'+rtrim(number*@i) as d from master.dbo.spt_values where type='p' and number>0) as a left join (select datediff(year,birthday,getdate()) as age,count(*) as total, b=count(case when sex=1 then 1 end),w=count(case when sex=0 then 1 end) from @t group by datediff(year,birthday,getdate())) as bon b.age >= a.mi and b.age < a.magroup by a.dhaving sum(isnull(b.total,0))<>0ORDER BY a.d/*d total b w------------------------- ----------- ----------- -----------18~27 9 5 427~36 1 1 036~45 1 1 045~54 1 0 19~18 1 0 1警告: 聚合或其他 SET 操作消除了空值。(5 行受影响)*/
[解决办法]
都没睡呢？我也优化一个。
create table #mytabe2(姓名 varchar(1),性别 varchar(2),出生日期 datetime)insert into #mytabe2select 'a','男','2010-02-01' union allselect 'b','男','2009-02-01' union allselect 'c','女','2008-02-01' union allselect 'd','女','2007-02-01' union allselect 'e','男','2006-02-01' union allselect 'f','男','2007-08-01' union allselect 'g','女','2008-03-01' union allselect 'h','女','2009-01-01'declare @ageduan int;set @ageduan=2 --LZ要定义的值select cast(年龄段*@ageduan AS varchar(20))+'岁到'+cast(@ageduan*(年龄段+1) as varchar(20))+'岁' as 年龄,sum(1) as 合计,SUM(case 性别 when '女' then 1 else 0 end ) as 女,SUM(case 性别 when '男' then 1 else 0 end ) as 男from(select datediff(year,出生日期,GETDATE())/@ageduan as 年龄段,性别 from #mytabe2) a group by 年龄段
[解决办法]
这样的表结构设计 可以通过写存储过程来调用
参数就是间隔的年份
