请教大家一个SQL查询的问题!谢谢.

[kyle7788]

问题是这样的, 有一张学生的信息表,具体如下:
create table tb_info
(
sid int, -- 学生的编号
cid int, -- 班级编号
sname varchar2(20), --学生的姓名
cname varchar2(10), --课程的名称
score number --学生的成绩
);
现在要实现的查询是, 找出各班级中语文成绩在前10名的学生的信息, 并且这些学生的数学成绩不能排在所在班的后10名中.
我给出的实现如下:

select *
from tb_info
where (sid, cid) in
(
  select aa.sid, aa.cid 
  from (
       select a.sid, a.cid, dense_rank() over(partition by cid order by score desc) rn1
       from tb_info a
       where a.cname = '语文'
  ) aa
  where rn1 <=10 and
        not exists 
        (
            select 1
            from (
               select b.sid, b.cid, dense_rank() over(partition by cid order by score) rn2
               from tb_info b
               where b.cname = '数学'
            ) bb
            where aa.sid = bb.sid and aa.cid = bb.cid and rn2<=10
        )
);

还有另外的一种写法是:

select * from tb_info
where (sid, cid) in
(
  select aa.sid, aa.cid 
  from (
       select a.sid, a.cid, dense_rank() over(partition by cid order by score desc) rn1
       from tb_info a
       where a.cname = '语文'
  ) aa,
  ( select b.sid, b.cid, dense_rank() over(partition by cid order by score) rn2
     from tb_info b
     where b.cname = '数学'
  ) bb
  where aa.sid = bb.sid and aa.cid = bb.cid and rn1<=10 and rn2 >10
); 

上面两种写法的执行计划是一样的.

请教大家有没有其它更好的写法. 谢谢了!

[vber1010]

with sx as 
(select * from(select sid, cid, dense_rank() over(partition by sid,cid order by score desc)sxpm
  from tb_info
 where cname = '数学') where sxpm<=10)
 select * from (select t.*,dense_rank() over(partition by t.sid,t.cid order by t.score desc)ywpm 
 from tb_info t where 
 t.cname='语文' and  not exists
  (select null from sx where sx.sid=t.sid and sx.cid=t.cid)) where ywpm<=10

[小灰狼W]

SELECT SID,cid,sname
FROM(
  SELECT SID,cid,sname,
    rank()OVER(PARTITION BY cid ORDER BY max(decode(cname,'语文',score)) DESC)rk1,
    rank()OVER(PARTITION BY cid ORDER BY max(decode(cname,'数学',score)))rk2
  FROM tb_info
  WHERE cname IN('语文','数学')
  GROUP BY SID,cid,sname
)
WHERE rk1<=10
  AND rk2>10;

[虫洞]

select *
from 
(select a.*
       ,b.cname cname2
       ,b.score score2
       ,dense_rank() over(partition by a.cid order by a.score desc) rn1
       ,dense_rank() over(partition by a.cid order by b.score) rn2
from tb_info a,tb_info b
where a.sid=b.sid and a.cid=b.cid
and a.cname='语文'
and b.cname='数学'
)a
where a.rn1<=10 and a.rn2>10
;

[vber1010]

哦 我的理解有误。我的查询是保证查出来的结果保证是10个学生。而楼主的需求结果可能会少于10个查询结果。

[chen_19821119]

select * from
(select * from tb_info where cname='语文' and sid not in
(select sid from
(select sid from tb_info where cname='数学' order by score) where rownum<11) order by score desc)
where rownum<11

[kyle7788]

谢谢各位的解答!受益匪浅!