日期:2014-05-18  浏览次数:20351 次

根据年龄段分组统计超难问题
有这样一个表如下字段
姓名 性别 出生日期

求如下结果根据一个变量来指定间距统计年龄如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 @t
select 'a','男','2010-02-01' union all
select 'b','男','2009-02-01' union all
select 'c','女','2008-02-01' union all
select 'd','女','2007-02-01' union all
select 'e','男','2006-02-01' union all
select 'f','男','2007-08-01' union all
select 'g','女','2008-03-01' union all
select 'h','女','2009-01-01'

declare @i int set @i=3

select 
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 all
select 
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 @t
union all
select 
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           3
3           2           1
0           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 w
from(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 b
on b.age >= a.mi and b.age < b.ma
group by a.d
having 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 int
set @i = 9

select a.d,sum(isnull(b.total,0)) as total,sum(isnull(b.b,0)) as b,sum(isnull(b.w,0)) as w
from(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 b
on b.age >= a.mi and b.age < a.ma
group by a.d
having sum(isnull(b.total,0))<>0
ORDER BY