数据库经典SQL笔试题-用一条sql查询出每门学科成绩都高于该学科平均分的学生姓名

1、表结构

CREATE TABLE student(NAME VARCHAR2(6),
                     subject VARCHAR2(6),
                     score NUMBER(3));
                     
INSERT INTO student(NAME,subject,score) VALUES('张三','语文',81); 
INSERT INTO student(NAME,subject,score) VALUES('张三','数学',75); 
INSERT INTO student(NAME,subject,score) VALUES('李四','语文',76); 
INSERT INTO student(NAME,subject,score) VALUES('李四','数学',90); 
INSERT INTO student(NAME,subject,score) VALUES('王五','语文',81); 
INSERT INTO student(NAME,subject,score) VALUES('王五','数学',100);

2.1、用一条sql查询出所有学科分数都大于80分的学生姓名

SELECT a.name
  FROM (SELECT NAME, MIN(score)
          FROM student
         GROUP BY NAME
        HAVING MIN(score) > 80) a;

2.2、用一条sql查询出每门学科都高于该学科平均分的学生姓名

SELECT b.name
  FROM (SELECT a.*
          FROM (SELECT NAME,
                       subject,
                       score,
                       round(AVG(score) over(PARTITION BY subject), 2) avgscore
                  FROM student) a
         WHERE a.score > a.avgscore) b
 GROUP BY b.name
HAVING COUNT(*) = (SELECT COUNT(DISTINCT subject) FROM student);

2.3 行转列

SELECT s.name 姓名, s.score 语文, s.ld1 数学
  FROM (SELECT s.*,
               lead(s.score, 1) over(PARTITION BY s.name ORDER BY s.subject DESC) ld1
          FROM student s) s
 WHERE s.subject = '语文';
 
SELECT s.name,
       SUM(decode(s.subject, '语文', s.score)) 语文,
       SUM(decode(s.subject, '数学', s.score)) 数学
  FROM student s
 GROUP BY s.name;