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

Question

问题是这样的, 有一张学生的信息表,具体如下:
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
); 上面两种写法的执行计划是一样的.请教大家有没有其它更好的写法. 谢谢了!

Answer 1

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

Answer 2

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;

Answer 3

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
;

Answer 4

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