SQL73 考试分数(二)

---

一、题目

牛客每次考试完，都会有一个成绩表(grade)，如下:

请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下:

二、解题

1.窗口函数

（1）

 SELECT id,job,score,avg(score) over(partition by job) as avgs
    FROM grade

每一行都有平均值的输出

从中找出 score > avg(score) 的行

SELECT id,job,score
FROM (
    SELECT id,job,score,avg(score) over(partition by job) as avgs
    FROM grade
) t
WHERE score >avgs
ORDER BY id;

2.连接查询表

SELECT *
FROM grade g1
JOIN (
    SELECT job,avg(score) as avgs
    FROM grade
    GROUP BY job
) t
ON g1.job = t.job

SELECT g1.id,g1.job,g1.score
FROM grade g1
JOIN (
    SELECT job,avg(score) as avgs
    FROM grade
    GROUP BY job
) t
ON g1.job = t.job
WHERE g1.score>t.avgs

3.WHERE 判断

SELECT g1.id,g1.job,g1.score
FROM grade g1
WHERE g1.score>(
    SELECT avg(score)
    FROM grade g2
    WHERE g1.job=g2.job
)