17,090
社区成员
发帖
与我相关
我的任务
分享
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
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
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); --分数相同
--写一个不用分析函数的
SELECT * FROM AA a)
WHERE 2 > (SELECT COUNT(1)
FROM AA b
WHERE b.课程号 = a.课程号
AND a.成绩 < b.成绩);
select 学号,课程号,成绩
from
(select 学号,课程号,成绩,
row_number()over(partition by 课程号 order by 成绩 desc) rn
from AA)
where rn<3
select 学号,课程号
from
(select 学号,课程号,row_number()over(partition by 课程号,成绩 order by 学号) rn from AA)
where rn<3