错了: select class,sum(case when sex=1 then 1 else 0 end) as 男, sum(case when sex=1 then 0 else 1 end) as 女 from student group by class
select class,count(s2,name),count(s1.name) from student s1 inner join (select class,name from student where sex=1)s2 on s1.class=s2.class where s1.sex=2 group by s1.class
select class,case sex when then '男' when 2 then '女' end as sex,count(*) as studentcount from student group by class ,sex
好象大家都是用SQL server的嘛,呵呵,没有用oracle的吗?:)
Haiwer(Haiwer) 的答案挺好,Oracle8能用。 GodHack(@烟花岁月@) :如果一个班全是男生(好比我们班),这个班就出不来了。Oracle7: select class,sum(F),sum(M) from ( select class,count(*) F, 0 M from student where sex=1 group by class union select class,0 F, count(*) M from student where sex=2 group by class ) group by class
我想这样最简单,但只是不符合结果 select class,sex,count(*) from student group by class,sex; 还可以用,就比较符合要求: select class,sum(decode(sex,'1',1,0)) f,sum(decode(sex,'1',0,1)) m from student group by class;
select class,sum(case when sex=1 then 1 else 0 end) as 男,
sum(case when sex=1 then 0 else 1 end) as 女
from student
group by class
from student s1
inner join (select class,name from student where sex=1)s2
on s1.class=s2.class
where s1.sex=2
group by s1.class
GodHack(@烟花岁月@) :如果一个班全是男生(好比我们班),这个班就出不来了。Oracle7:
select class,sum(F),sum(M) from (
select class,count(*) F, 0 M from student where sex=1
group by class
union
select class,0 F, count(*) M from student where sex=2
group by class
)
group by class
select class,sex,count(*) from student
group by class,sex;
还可以用,就比较符合要求:
select class,sum(decode(sex,'1',1,0)) f,sum(decode(sex,'1',0,1)) m
from student
group by class;
呵呵,考虑不周,不过你班也太惨了吧