请教实现相同功能的更高效的SQL写法, 谢谢!

[kyle7788]

要实现的功能是:
要求查询出所选某门课程的男生与女生人数相同的那些课程名

相关的脚本如下:

-- Create table 其实应该是 class 表来着, 暂时就用下面的脚本吧
create table GLOBAL_RESOURCE
(
  CLASS VARCHAR2(20), --课程名
  NAME  VARCHAR2(10), --学生姓名
  SEX   VARCHAR2(6)   -- 性别 male or female
);

-- 测试数据
insert into global_resource (CLASS, NAME, SEX)
values ('A', '张三', 'male');

insert into global_resource (CLASS, NAME, SEX)
values ('A', '李四', 'male');

insert into global_resource (CLASS, NAME, SEX)
values ('B', '王五', 'male');

insert into global_resource (CLASS, NAME, SEX)
values ('A', '赵六', 'female');

insert into global_resource (CLASS, NAME, SEX)
values ('A', '孙七', 'female');

insert into global_resource (CLASS, NAME, SEX)
values ('B', '陈二', 'male');

insert into global_resource (CLASS, NAME, SEX)
values ('B', '唐九', 'female');

insert into global_resource (CLASS, NAME, SEX)
values ('C', '柯十', 'male');

insert into global_resource (CLASS, NAME, SEX)
values ('C', '袁二', 'female');

我自己写的SQL是:

select a.class
from (select class, count(1) cnt
        from global_resource
        where sex = 'male'
        group by class
       ) a,  
       ( select class, count(1) cnt
        from global_resource
        where sex = 'female'
        group by class
       ) b
where a.class = b.class and a.cnt = b.cnt;

CLASS
--------------------
A
C

请教其它更高效的写法, 感觉我上面的写法查表的次数有点多. 谢谢!

[心中的彩虹]

SQL> edi
已写入 file afiedt.buf

  1  select class
  2  from global_resource
  3  group by class
  4* having sum(decode(sex,'male',1))=sum(decode(sex,'female',1))
SQL> /

CLASS
--------------------
A
C

[qldsrx]

[Quote=引用 1 楼 wkc168 的回复:]
SQL code



SQL> edi
已写入 file afiedt.buf

1 select class
2 from global_resource
3 group by class
4* having sum(decode(sex,'male',1))=sum(decode(sex,'female',1))
SQL> /

CLASS
……
[/Quote]
不错，这样就可以做到了。

[kyle7788]

[Quote=引用 1 楼 wkc168 的回复:]
SQL code



SQL> edi
已写入 file afiedt.buf

1 select class
2 from global_resource
3 group by class
4* having sum(decode(sex,'male',1))=sum(decode(sex,'female',1))
SQL> /

CLASS
……
[/Quote]
谢谢!