面试题求解

Question

//学生成绩表
create table stu_score
(
  id    number(20) not null,//学号
  name           varchar2(128) not null,//姓名
  age            number(10) not null,//年龄
  match_score    number(10) not null,//数学成绩
  chinese_score  number(10) not null,//语文成绩
  english_score  number(10) not null,//外语成绩
  constraint PK_KEYWORD_UNIQUE_TEMP primary key ("id")
)1.查询数学、语文，外语三门成绩都是前10名的学生信息。2.查询数学成绩前10名与后10名的学生信息。3.查询数学成绩前10名与语文成绩后10名的学生信息。提示：注意查询结果的包含关系，需过滤掉重复记录

Answer 1

--1
SELECT ID, NAME, AGE
  FROM (SELECT ID,
               NAME,
               AGE,
               MATCH_SCORE,
               DENSE_RANK() OVER(ORDER BY MATCH_SCORE DESC) M_1,
               DENSE_RANK() OVER(ORDER BY MATCH_SCORE) M_2,
               CHINESE_SCORE,
               DENSE_RANK() OVER(ORDER BY DENSE_RANK DESC) C_1,
               DENSE_RANK() OVER(ORDER BY DENSE_RANK) C_2,
               ENGLISH_SCORE,
               DENSE_RANK() OVER(ORDER BY ENGLISH_SCORE DESC) E_1,
               DENSE_RANK() OVER(ORDER BY ENGLISH_SCORE) E_2
          FROM STU_SCORE)
 WHERE M_1 <= 10
   AND C_1 <= 10
   AND E_1 <= 10;--2
SELECT ID, NAME, AGE
  FROM (SELECT ID,
               NAME,
               AGE,
               MATCH_SCORE,
               DENSE_RANK() OVER(ORDER BY MATCH_SCORE DESC) M_1,
               DENSE_RANK() OVER(ORDER BY MATCH_SCORE) M_2,
               CHINESE_SCORE,
               DENSE_RANK() OVER(ORDER BY DENSE_RANK DESC) C_1,
               DENSE_RANK() OVER(ORDER BY DENSE_RANK) C_2,
               ENGLISH_SCORE,
               DENSE_RANK() OVER(ORDER BY ENGLISH_SCORE DESC) E_1,
               DENSE_RANK() OVER(ORDER BY ENGLISH_SCORE) E_2
          FROM STU_SCORE)
 WHERE M_1 <= 10
   OR  M_2 <= 10;--3
SELECT ID, NAME, AGE
  FROM (SELECT ID,
               NAME,
               AGE,
               MATCH_SCORE,
               DENSE_RANK() OVER(ORDER BY MATCH_SCORE DESC) M_1,
               DENSE_RANK() OVER(ORDER BY MATCH_SCORE) M_2,
               CHINESE_SCORE,
               DENSE_RANK() OVER(ORDER BY DENSE_RANK DESC) C_1,
               DENSE_RANK() OVER(ORDER BY DENSE_RANK) C_2,
               ENGLISH_SCORE,
               DENSE_RANK() OVER(ORDER BY ENGLISH_SCORE DESC) E_1,
               DENSE_RANK() OVER(ORDER BY ENGLISH_SCORE) E_2
          FROM STU_SCORE)
 WHERE M_1 <= 10
   OR  C_2 <= 10;

Answer 2

--1.查询数学、语文，外语三门成绩都是前10名的学生信息。
SELECT  ID,NAME FROM 
(SELECT ID,NAME,
row_number() over(ORDER BY match_score DESC) rn1,
row_number() over(ORDER BY chinese_score DESC) rn2, 
row_number() over(ORDER BY english_score DESC) rn3 
FROM  stu_score
)
WHERE rn1<=10 AND rn2<=10 AND rn3<=10;

Answer 3

--2.查询数学成绩前10名与后10名的学生信息。
SELECT  ID,NAME FROM 
(SELECT ID,NAME,
row_number() over(ORDER BY match_score DESC) rn1,
row_number() over(ORDER BY match_score) rn2
FROM  stu_score
)
WHERE rn1<=10 OR rn2<=10

Answer 4

--3.查询数学成绩前10名与语文成绩后10名的学生信息。
SELECT  ID,NAME FROM 
(SELECT ID,NAME,
row_number() over(ORDER BY match_score DESC) rn1,
row_number() over(ORDER BY chinese_score) rn2
FROM  stu_score
)
WHERE rn1<=10 AND rn2<=10