SQL 一条SQL语句 统计 各班总人数、男女各总人数 、该班级男女 比例

1 select count(*) as numOfTotal,
2 sum(case when t.[gender]='male' then 1 else 0 end) as numOfMale , --男生人数
3 sum(case when t.[gender]='female' then 1 else 0 end) numOfFemale, --女生人数
4 sum(case when t.[gender]='male' then 1 else 0 end)*1.0/count(*) as percentOfMale,
5 sum(case when t.[gender]='female' then 1 else 0 end)*1.0/count(*) as perscentOfFemale
6 from
7 (SELECT a.[userid],a.[module],a.[amount],b.[name],b.[gender]
8   FROM [PracticeSQL].[dbo].[module] a inner join [PracticeSQL].[dbo].[user] b
9   on a.[userid]=b.[userid]) t