select 学号,课程号 from (select 学号,课程号,row_number()over(partition by 课程号,成绩 order by 学号) rn from AA) where rn<3
select t.* from aa t where 成绩 in (select 成绩 from tb where 课程号 = t.课程号 and rownum <= 2 order by 成绩 desc)
--如果不考虑分数重复,用RANK() select m.学号 , m.课程号 , m.成绩 from ( select t.* , RANK() over(partition by 课程号 order by 成绩 desc) px from aa t ) m where px <= 2 order by m.课程号 , m.成绩 desc--如果考虑分数重复,用DENSE_RANK() select m.学号 , m.课程号 , m.成绩 from ( select t.* , DENSE_RANK() over(partition by 课程号 order by 成绩 desc) px from aa t ) m where px <= 2 order by m.课程号 , m.成绩 desc
select 学号,课程号,成绩 from (select 学号,课程号,成绩, row_number()over(partition by 课程号 order by 成绩 desc) rn from AA) where rn<3
分析函数就行啊 select * from ( select 学号,课程号,成绩,rank()over(partition by 课程号 order by 成绩 desc) rn from table ) where rn < 3
--写一个不用分析函数的 SELECT * FROM AA a) WHERE 2 > (SELECT COUNT(1) FROM AA b WHERE b.课程号 = a.课程号 AND a.成绩 < b.成绩);
测试数据:CREATE TABLE T161 ( SNO NUMBER(2), CNO NUMBER(2), Score NUMBER(3) ); INSERT INTO T161 VALUES(1, 1, 90); INSERT INTO T161 VALUES(2, 2, 89); INSERT INTO T161 VALUES(3, 2, 80); INSERT INTO T161 VALUES(4, 1, 81); INSERT INTO T161 VALUES(5, 1, 85); INSERT INTO T161 VALUES(6, 2, 100);INSERT INTO T161 VALUES(7, 1, 90); --分数相同 测试结果:
标准sql的实现: select * from aa a where a.stuid in ( select top 2 stuid from aa where a.couid=couid order by score desc ) order by couid,score desc在sql server 2000运行得到预期结果
with tt as( select 1 "学号",1 "课程号",90 "成绩" from dual union all select 2,2,89 from dual union all select 3,2,80 from dual union all select 4,1,81 from dual union all select 5,1,85 from dual union all select 6,2,100 from dual )select * from (select "学号","课程号",rank() over (partition by "课程号" order by "成绩" desc) rn from tt) where rn<=2
from
(select 学号,课程号,row_number()over(partition by 课程号,成绩 order by 学号) rn from AA)
where rn<3
select m.学号 , m.课程号 , m.成绩 from
(
select t.* , RANK() over(partition by 课程号 order by 成绩 desc) px from aa t
) m
where px <= 2
order by m.课程号 , m.成绩 desc--如果考虑分数重复,用DENSE_RANK()
select m.学号 , m.课程号 , m.成绩 from
(
select t.* , DENSE_RANK() over(partition by 课程号 order by 成绩 desc) px from aa t
) m
where px <= 2
order by m.课程号 , m.成绩 desc
select 学号,课程号,成绩
from
(select 学号,课程号,成绩,
row_number()over(partition by 课程号 order by 成绩 desc) rn
from AA)
where rn<3
select * from (
select 学号,课程号,成绩,rank()over(partition by 课程号 order by 成绩 desc) rn
from table
)
where rn < 3
SELECT * FROM AA a)
WHERE 2 > (SELECT COUNT(1)
FROM AA b
WHERE b.课程号 = a.课程号
AND a.成绩 < b.成绩);
(
SNO NUMBER(2),
CNO NUMBER(2),
Score NUMBER(3)
);
INSERT INTO T161 VALUES(1, 1, 90);
INSERT INTO T161 VALUES(2, 2, 89);
INSERT INTO T161 VALUES(3, 2, 80);
INSERT INTO T161 VALUES(4, 1, 81);
INSERT INTO T161 VALUES(5, 1, 85);
INSERT INTO T161 VALUES(6, 2, 100);INSERT INTO T161 VALUES(7, 1, 90); --分数相同
测试结果:
select * from aa a where a.stuid in (
select top 2 stuid from aa where a.couid=couid order by score desc
)
order by couid,score desc在sql server 2000运行得到预期结果
with tt as(
select 1 "学号",1 "课程号",90 "成绩" from dual
union all
select 2,2,89 from dual
union all
select 3,2,80 from dual
union all
select 4,1,81 from dual
union all
select 5,1,85 from dual
union all
select 6,2,100 from dual
)select * from (select "学号","课程号",rank() over (partition by "课程号" order by "成绩" desc) rn from tt) where rn<=2