已知关系模式
S (SNO, SNAME)学生关系。SNO为学生学号，SNAME为学生姓名。
C (CNO, CNAME, CTEACHER)课程关系。CNO课程编号，CNAME课程名，CTEACHER
任教老师。
SC (SNO, CNO, SCGRADE)选课关系。SNO为学生学号，CNO为课程编号，SCGRADE为
学生成绩。
题：列出各科成绩前三名的学生学号（如有名次并列相同，则按学号升序，取第一个学号）
统计格式如下：
解答如下：
select CNO 课程编号,
(case when tno1=0 then '没有选该门课程的学生了！' else to_char(tno1) end) 第一名学号,
(case when tno2=0 then '没有选该门课程的学生了！' else to_char(tno2) end) 第二名学号,
(case when tno3=0 then '没有选该门课程的学生了！' else to_char(tno3) end) 第三名学号
from
(select CNO,nvl(max(no1), 0) tno1,nvl(max(no2), 0) tno2,nvl(max(no3), 0) tno3
from (
select CNO,
(case when rn=1 then SNO else null end) no1,
(case when rn=2 then SNO else null end) no2,
(case when rn=3 then SNO else null end) no3
from (
select
CNO,
SNO,
row_number() over(partition by CNO order by SCGRADE desc) rn
from SC
order by CNO, rn)
where rn<=3)
group by CNO
order by 1)
【建议：建表然后插入数据】
--第一步：建表
create table S(
SNO number(30) primary key,
SNAME varchar2(30)
)
create table C(
CNO number(30) primary key,
CNAME varchar2(30),
CTEACHER varchar2(30)
)
create table SC(
SNO number(30),
CNO number(30),
SCGRADE number(30),
foreign key(SNO) references S(SNO),
foreign key(CNO) references C(CNO)
)
--第二步：插入数据
insert into S values(1, '林芬芬');
insert into S values(2, '邹永勇');
insert into S values(3, '钟菲菲');
insert into S values(4, '肖倩倩');
insert into S values(5, '佩雷雷');
insert into S values(6, '孙浩浩');
insert into S values(7, '潘甜甜');
insert into S values(8, '杨芳芳');
insert into S values(9, '秦川川');
insert into S values(10, '吴薇薇');
insert into S values(11, '李明明');
insert into S values(12, '王大大');
insert into S values(13, '孙海海');
insert into S values(14, '张飞飞');
insert into S values(15, '孙尚香');
insert into S values(16, '曹仁仁');
select * from S;
insert into C values(1, '高等数学', '李明');
insert into C values(2, '编译原理', '周雄');
insert into C values(3, '计算机', '李开复');
insert into C values(4, '音乐', '金莎莎');
insert into C values(5, '计算数学', '周恩恩');
insert into C values(6, 'JAVA语言', '毛东东');
insert into C values(7, 'C语言', '欧阳锋');
insert into C values(8, '软件工程', '曹操操');
insert into C values(9, '数值方法', '刘备备');
insert into C values(10, '离散数学', '孙权权');
insert into C values(11, '信息论', '宋祖英');
insert into C values(12, '现代密码学', '范冰冰');
insert into C values(13, 'Shell编程', '胡涛涛');
select * from C;
commit;
insert into SC values(16, 13, 80);
insert into SC values(16, 10, 94);
insert into SC values(16, 8, 80);
insert into SC values(16, 3, 46);
insert into SC values(16, 1, 45);
insert into SC values(15, 3, 88);
insert into SC values(14, 3, 0);
insert into SC values(14, 5, 77);
insert into SC values(13, 1, 69);
insert into SC values(13, 2, 55);
insert into SC values(13, 6, 13);
insert into SC values(13, 9, 35);
insert into SC values(12, 12, 43);
insert into SC values(12, 8, 28);
insert into SC values(12, 7, 78);
insert into SC values(12, 6, 90);
insert into SC values(11, 3, 74);
insert into SC values(11, 1, 80);
insert into SC values(10, 4, 86);
insert into SC values(9, 1, 62);
insert into SC values(8, 3, 58);
insert into SC values(7, 13, 68);
insert into SC values(7, 12, 50);
insert into SC values(6, 3, 20);
insert into SC values(6, 6, 20);
insert into SC values(5, 1, 65);
insert into SC values(4, 13, 55);
insert into SC values(4, 5, 75);
insert into SC values(3, 3, 12);
insert into SC values(2, 3, 10);
insert into SC values(2, 1, 78);
insert into SC values(1, 1, 90);
insert into SC values(1, 2, 90);
insert into SC values(1, 3, 90);
insert into SC values(1, 4, 90);
insert into SC values(1, 5, 90);
insert into SC values(1, 6, 90);
insert into SC values(1, 7, 90);
insert into SC values(1, 8, 90);
insert into SC values(1, 9, 90);
insert into SC values(1, 10, 90);
insert into SC values(1, 11, 90);
insert into SC values(1, 12, 90);
insert into SC values(1, 13, 90);
select * from SC;
- 5楼wqs151920956333天前 09:08
- 、 、 不明白。 、
- 4楼chchen193天前 08:44
- 不是很明白咯
- 3楼xvshu3天前 21:55
- 继续努力加油
- 2楼sunysay3天前 21:48
- 终于看到一篇能让人明白的SQL文章
- 1楼mazhaojuan3天前 18:44
- 还是没太明白！