22,210
社区成员
发帖
与我相关
我的任务
分享
create table CC
(
class int,
sex varchar(10)
)
insert into CC select 1,'W'
insert into CC select 2,'W'
insert into CC select 3,'M'
insert into CC select 1,'M'
insert into CC select 1,'M'
insert into CC select 1,'M'
insert into CC select 2,'W'
insert into CC select 3,'M'
insert into CC select 1,'W'
insert into CC select 3,'W'
insert into CC select 1,'W'
insert into CC select 3,'M'
insert into CC select 1,'M'
select class as ' 班级' ,
sum(case when sex='W' then 1 else 0 end) as '女生人数',
sum(case when sex='M' then 1 else 0 end) as '男生人数',
count(*) as '总人数'
from CC group by class
select 班级,count(1) as 总人数,
sum(case when SEX='m' then 1 else 0 end ) as 男生数,
sum(case when SEX='w' then 1 else 0 end ) as 女生数
from 学校
group by 班级
--这个可以的。
select 班级,count(1) as 总人数,
sum(case when SEX='m' then 1 else 0 end ) as 男生数,
sum(case when SEX='w' then 1 else 0 end ) as 女生数
from 学校
group by 班级
declare @school table (class int,sex varchar(10))
insert into @school
select 1, 'w' union all
select 2, 'm' union all
select 1, 'm' union all
select 3, 'm' union all
select 2, 'w' union all
select 1, 'w' union all
select 2, 'm' union all
select 1, 'm' union all
select 3, 'w'
select class,count(*) 班级人数
,sum(case when sex='w' then 1 else 0 end) woman
,sum(case when sex='m' then 1 else 0 end) man
from @school group by class