select class,sum(case when sex='boy' then 1 else 0 end) as boys,
sum(case when sex='girl' then 1 else 0 end) as girls
from students
group by class
sum(case when sex='girl' then 1 else 0 end) as girls
from students
group by class
sum(case when sex='girl' then 1 else 0 end ) as girls from students
group by class
(select class,count(sex) as boy from students where sex='boy' group by class)a,
(select class,count(sex) as girl from students where sex='girl' group by class)b
where a.Class=b.Class
呵呵绝招出击,在SQL SERVER2000中通过!
select class,sum(case sex when 1 then 1 else 0 end) as "Boys",
sum(case sex when 0 then 1 else 0 end) as "Grils"
from student
group by class
(select class,count(sex) as boy
from students
where sex='boy'
group by class) as Boy
full join
(select class,count(sex) as girl
from students
where sex='girl'
on boy.class=girl.class
group by class) as Girl
sum(case when sex = 'girl' then 1 else 0 end) as girl
from students
group by class