查询各科成绩前三名的记录:(不考虑成绩并列情况)
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
constraint pk_sc primary key (sno,cno)
)
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');
代码:
SELECT t1.sno,t1.cno,Score
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.cno= cno)
ORDER BY t1.cno,score desc
我很疑惑WHERE t1.cno= cno这个是什么意思,大神们能不能详细讲解整个代码,最好说一下写这些的思路是怎样的?本人菜鸟。