Select Age,sum(case when sex = '男' then 1 else 0 end) as 男生人数,
sum(case when sex = '男' and task = 1 then 1 else 0 end)*100.0/sum(case when sex = '男' then 1 else 0 end) as 男完成的人数%,
sum(case when sex = '女' then 1 else 0 end) as 女生人数,
sum(case when sex = '女' and task = 1 then 1 else 0 end)*100.0/sum(case when sex = '女' then 1 else 0 end) as 女完成的人数%,
count(*) as 合计人数,
sum(case when task = 1 then 1 else 0 end)*100.0/count(*) as 合计%
from 表 group by age
sum(case when sex = '男' and task = 1 then 1 else 0 end)*100.0/sum(case when sex = '男' then 1 else 0 end) as 男完成的人数%,
sum(case when sex = '女' then 1 else 0 end) as 女生人数,
sum(case when sex = '女' and task = 1 then 1 else 0 end)*100.0/sum(case when sex = '女' then 1 else 0 end) as 女完成的人数%,
count(*) as 合计人数,
sum(case when task = 1 then 1 else 0 end)*100.0/count(*) as 合计%
from 表 group by age
,男生人数=sum(case sex when '男' then 1 else 0 end)
,[男生完成的人数%]=cast(cast(sum(case when sex='男' and task=1 then 1.0 else 0.0 end)
/sum(case sex when '男' then 1 else 0 end)*100 as decimal(20,2)) as varchar)+'%'
,女生人数=sum(case sex when '女' then 1 else 0 end)
,[女生完成的人数%]=cast(cast(sum(case when sex='女' and task=1 then 1.0 else 0.0 end)
/sum(case sex when '男' then 1 else 0 end)*100 as decimal(20,2)) as varchar)+'%'
,合计人数=sum(1)
,合计%=cast(cast(sum(case when task=1 then 1.0 else 0.0 end)
/sum(1)*100 as decimal(20,2)) as varchar)+'%'
from 表 group by age
,男生人数=sum(case sex when '男' then 1 else 0 end)
,[男生完成的人数%]=cast(cast(sum(case when sex='男' and task=1 then 1.0 else 0.0 end)
/sum(case sex when '男' then 1 else 0 end)*100 as decimal(20,2)) as varchar)+'%'
,女生人数=sum(case sex when '女' then 1 else 0 end)
,[女生完成的人数%]=cast(cast(sum(case when sex='女' and task=1 then 1.0 else 0.0 end)
/sum(case sex when '男' then 1 else 0 end)*100 as decimal(20,2)) as varchar)+'%'
,合计人数=sum(1)
,[合计%]=cast(cast(sum(case when task=1 then 1.0 else 0.0 end)
/sum(1)*100 as decimal(20,2)) as varchar)+'%'
from 表 group by age
select age '年龄' ,
count(case when sex = '男' then 1 end) as '男生人数',
count(case when (task = 1 and sex = '男') then 1 end) * 1.0/count(case
when sex = '男' then 1 end) as '男完成的人数%',
count(case when sex = '女' then 1 end) as '女生人数',
count(case when (task = 1 and sex = '女') then 1 end) * 1.0/count(case
when sex = '女' then 1 end) as '女完成的人数%',
count(*) as '合计人数',
count(case when task = 1 then 1 end) * 1.0/count(*) as '合计%'
from 表
group by age
select age '年龄' ,
count(case when sex = '男' then 1 end) as '男生人数',
(case when count(case when sex = '男' then 1 end) = 0 then 0
else count(case when (task = 1 and sex = '男') then 1 end) *
100.0/count(case when sex = '男' then 1 end) end)
as '男完成的人数%',
count(case when sex = '女' then 1 end) as '女生人数',
(case when count(case when sex = '女' then 1 end) = 0 then 0
else count(case when (task = 1 and sex = '女') then 1 end) *
100.0/count(case when sex = '女' then 1 end) end)
as '女完成的人数%',
count(*) as '合计人数',
count(case when task = 1 then 1 end) * 1.0/count(*) as '合计%'
from 表
group by age
sum(case when sex = '男' and task = 1 then 1 else 0 end)*100.0/sum(case when sex = '男' then 1 else 0 end) as 男完成的人数%,
sum(case when sex = '女' then 1 else 0 end) as 女生人数,
sum(case when sex = '女' and task = 1 then 1 else 0 end)*100.0/sum(case when sex = '女' then 1 else 0 end) as 女完成的人数%,
count(*) as 合计人数,
sum(case when task = 1 then 1 else 0 end)*100.0/count(*) as 合计%
from A group by A.age
union all
Select '合計' 年齡,sum(case when sex = '男' then 1 else 0 end) as 男生人数,
sum(case when sex = '男' and task = 1 then 1 else 0 end)*100.0/sum(case when sex = '男' then 1 else 0 end) as 男完成的人数%,
sum(case when sex = '女' then 1 else 0 end) as 女生人数,
sum(case when sex = '女' and task = 1 then 1 else 0 end)*100.0/sum(case when sex = '女' then 1 else 0 end) as 女完成的人数%,
count(*) as 合计人数,
sum(case when task = 1 then 1 else 0 end)*100.0/count(*) as 合计%
from A