select class,总人数=count(*)
,[t1+男]=sum(case when type='t1' and sex='男' then 1 else 0 end)
,[t1+女]=sum(case when type='t1' and sex='女' then 1 else 0 end)
,[t2+男]=sum(case when type='t2' and sex='男' then 1 else 0 end)
,[t2+女]=sum(case when type='t2' and sex='女' then 1 else 0 end)
,平均年龄=cast(sum(datediff(month,birthday,getdate()))/12 as varchar)
+'/'+cast(sum(datediff(month,birthday,getdate()))%12 as varchar)
from 表a
group by class
,[t1+男]=sum(case when type='t1' and sex='男' then 1 else 0 end)
,[t1+女]=sum(case when type='t1' and sex='女' then 1 else 0 end)
,[t2+男]=sum(case when type='t2' and sex='男' then 1 else 0 end)
,[t2+女]=sum(case when type='t2' and sex='女' then 1 else 0 end)
,平均年龄=cast(sum(datediff(month,birthday,getdate()))/12 as varchar)
+'/'+cast(sum(datediff(month,birthday,getdate()))%12 as varchar)
from 表a
group by class
declare @s varchar(8000)
set @s=''
select @s=@s+',['+type+'+男]=sum(case when type='''+type
+''' and sex=''男'' then 1 else 0 end),['+type
+'+女]=sum(case when type='''+type
+''' and sex=''女'' then 1 else 0 end)'
from 表a
group by type
exec('
select class,总人数=count(*)'+@s+'
,平均年龄=cast(avg(datediff(month,birthday,getdate()))/12 as varchar)
+''(''+cast(avg(datediff(month,birthday,getdate()))%12 as varchar)
+''/12)''
from 表a
group by class
')
create table 表a(ID int,name varchar(10),class varchar(10),type varchar(10),sex varchar(10),birthday datetime)
insert 表a select 1,'a1','b1','t1','男','1994-5-1'
union all select 2,'a2','b1','t2','女','1995-8-1'
union all select 3,'a3','b2','t1','女','1994-6-3'
union all select 4,'a4','b1','t2','男','1995-8-1'
union all select 5,'a5','b2','t1','女','1994-6-3'
go--查询
declare @s varchar(8000)
set @s=''
select @s=@s+',['+type+'+男]=sum(case when type='''+type
+''' and sex=''男'' then 1 else 0 end),['+type
+'+女]=sum(case when type='''+type
+''' and sex=''女'' then 1 else 0 end)'
from 表a
group by type
exec('
select class,总人数=count(*)'+@s+'
,平均年龄=cast(avg(datediff(month,birthday,getdate()))/12 as varchar)
+''(''+cast(avg(datediff(month,birthday,getdate()))%12 as varchar)
+''/12)''
from 表a
group by class
')
go--删除测试
drop table 表a/*--测试结果class 总人数 t1+男 t1+女 t2+男 t2+女 平均年龄
------ ------ ----- ------ ------ ------ ---------
b1 3 1 0 1 1 9(4/12)
b2 2 0 2 0 0 10(1/12)--*/
平均年龄=cast(avg(datediff(month,birthday,getdate()))/12 as varchar)
+''(''+cast(avg(datediff(month,birthday,getdate()))%12 as varchar)
avg(datediff(month,birthday,getdate())) --所有人的平均年龄(月为单位)
avg(datediff(month,birthday,getdate()))/12 --平均年龄的年份
avg(datediff(month,birthday,getdate()))%12 --平均年龄的月份