有这样一个表如下字段
姓名 性别 出生日期
求如下结果根据一个变量来指定间距统计年龄如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
求一个高效快速的算法，不胜感激
------解决方案--------------------
- SQL code
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 [email protected] then 1 else 0 end),sum(case when datediff([year],出生日期,getdate()) between @i+1 and [email protected] and 性别='男' then 1 else 0 end) as 男,sum(case when datediff([year],出生日期,getdate()) between @i+1 and [email protected] and 性别='女' then 1 else 0 end) as 女from @tunion allselect sum(case when datediff([year],出生日期,getdate()) between [email protected]+1 and [email protected] then 1 else 0 end),sum(case when datediff([year],出生日期,getdate()) between [email protected]+1 and [email protected] and 性别='男' then 1 else 0 end) as 男,sum(case when datediff([year],出生日期,getdate()) between [email protected]+1 and [email protected] and 性别='女' then 1 else 0 end) as 女from @t/*合计 男 女----------- ----------- -----------5 2 33 2 10 0 0*/
------解决方案--------------------
- SQL code
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)[email protected] as mi,[email protected] as ma,rtrim((number-1)[email protected])+'~'+rtrim([email protected]) 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
------解决方案--------------------
- SQL code
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)[email protected] as mi,[email protected] as ma,rtrim((number-1)[email protected])+'~'+rtrim([email protected]) 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 行受影响)*/