create table T(Gender char(1), [Section] int) insert T select 'M', 1 union all select 'M', 1 union all select 'F', 1 union all select 'F', 2 union all select 'M', 2 union all select 'M', 1 union all select 'M', 2 union all select 'F', 3 union all select 'M', 2 union all select 'M', 3 union all select 'F', 3select section, count(*) as MaleNum from T where Gender = 'M' group by section--result section MaleNum ----------- ----------- 1 3 2 3 3 1(3 row(s) affected)
select section, count(*) as MaleNum from table where Gender = 'M' group by section
问题是如果某个section没有男生,那你这个就不会返回任何结果。我要的结果是0。
create table T(Gender char(1), [Section] int) insert T select 'M', 1 union all select 'M', 1 union all select 'F', 1 union all select 'F', 2 union all select 'M', 2 union all select 'M', 1 union all select 'M', 2 union all select 'F', 3 union all select 'M', 2 union all select 'M', 3 union all select 'F', 3 union all select 'F', 4 --新加的记录
select distinct [Section], MaleNum=(select count(*) from T where [Section]=tmp.[Section] and Gender='M') from T as tmp--result Section MaleNum ----------- ----------- 1 3 2 3 3 1 4 0(4 row(s) affected)
insert T select 'M', 1
union all select 'M', 1
union all select 'F', 1
union all select 'F', 2
union all select 'M', 2
union all select 'M', 1
union all select 'M', 2
union all select 'F', 3
union all select 'M', 2
union all select 'M', 3
union all select 'F', 3select section, count(*) as MaleNum from T
where Gender = 'M'
group by section--result
section MaleNum
----------- -----------
1 3
2 3
3 1(3 row(s) affected)
where Gender = 'M'
group by section
insert T select 'M', 1
union all select 'M', 1
union all select 'F', 1
union all select 'F', 2
union all select 'M', 2
union all select 'M', 1
union all select 'M', 2
union all select 'F', 3
union all select 'M', 2
union all select 'M', 3
union all select 'F', 3
union all select 'F', 4 --新加的记录
select distinct [Section],
MaleNum=(select count(*) from T where [Section]=tmp.[Section] and Gender='M')
from T as tmp--result
Section MaleNum
----------- -----------
1 3
2 3
3 1
4 0(4 row(s) affected)