select * from (select class,count(name) num from student where sex=male group by class) male,(select class,count(name) num from student where sex=male group by class) female where male.class=female.class and male.num=female.num;
调试欢乐多
SQL> create table student
2 (
3 name varchar(10),
4 sex varchar(10),
5 class varchar(10)
6 );Table createdSQL> insert into student values ('小三','female','一班');1 row insertedSQL> insert into student values ('小红','female','三班');1 row insertedSQL> insert into student values ('小黑','male','一班');1 row insertedSQL> insert into student values ('小张','female','二班');1 row insertedSQL> insert into student values ('小四','female','三班');1 row insertedSQL> insert into student values ('小白','male','二班');1 row insertedSQL> insert into student values ('小王','male','三班');1 row insertedSQL> insert into student values ('小李','male','三班');1 row insertedSQL> commit;Commit completeSQL> select male.class,male.num from (select class,count(name) num from student where sex='male' group by class) male,
2 (select class,count(name) num from student where sex='female' group by class) female
3 where male.class=female.class and male.num=female.num;CLASS NUM
---------- ----------
二班 1
三班 2
一班 1