数据库开发试题总结

来源：互联网发布：looks for mac 编辑：程序博客网时间：2024/06/06 01:43

STU_ID STU_NAME     STU_AGE  学生表student---------- -------   -------       1001 张三          23             1002 李四          19             1003 王五          22             1004 赵六          25             1005 小王          24             1006 小明          22  -------------------------------LESSION_ID LESSON_NAME  课程表lession---------- -----------         1   语文         2   数学         3   英语         4   物理         5   化学     -------------------------------     STU_ID GRADE LESSION_ID  分数表score---------- ----- ----------      1001    98          1      1002    78          2      1003    90          4      1004    89          5      1005    92          4      1006    94          5      1001    57          4      1004    87          2      1001    98          2      1002    78          3      1003    90          1      1004    89          2      1005    92          3      1006    94          2      1004    87          1      1001    98          1      1002    78          2      1003    90          4      1004    89          5      1005    92          4如何得到如下格式：------------------------------------科目  第一名(姓名+分数) 第二名(姓名+分数) 第三名(姓名+分数)语文    张三，98数学    英语物理化学

提供了数据创建码 create table Lession(    lession_id number primary key,    lesson_name varchar2(8));create table Student (       stu_id number primary key , stu_name varchar2(8), stu_age number(4), class_id number(4));create table Score(       stu_id number,   grade number(4), lession_id number(4));alter table Score add constraint fk_score_lession foreign key(lession_id)  references Lession(lession_id);alter table Score add constraint fk_score_student foreign key(stu_id)  references Student(stu_id);

select          LESSON_NAME as '科目',                   max(case when sequence = 1 then NameGrade else null end) as '第一名(姓名+分数)',                   max(case when sequence = 2 then NameGrade else null end) as '第二名(姓名+分数)',                   max(case when sequence = 3 then NameGrade else null end) as '第三名(姓名+分数)'(  select LESSON_NAME,STU_NAME+','+convert(varchar,GRADE) as NameGrade,sequence    (select b.LESSON_NAME,c.STU_NAME,a.GRADE,row_number() over(order by a.GRADE Desc, c.STU_NAME asc) as sequence from score a    inner join lession b on (a.LESSION_ID = b.LESSION_ID)    inner join student c on (a.STU_ID  = c.STU_ID) ) d  where sequence < 4) eorder by (select LESSION_ID from lession f where f.LESSON_NAME  = e.LESSON_NAME)
order by case(when LESSON_NAME = '语文' then 1,                       when LESSON_NAME = '数学' then 2,                       when LESSON_NAME = '英语' then 3,               when LESSON_NAME = '物理' then 4,               when LESSON_NAME = '化学' then 5,                       else 9999 end)

0 0