题解 | #考试分数(二)#

问题描述：请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序
方案1：内连接
第一步：分组求算部门的平均分

SELECT job,AVG(score)
FROM grade
GROUP BY job

第二步：第一步作为临时表t联结grade，where过滤

SELECT g.id,g.job,g.score
FROM grade g
JOIN (SELECT job,AVG(score) AS avg_sco
            FROM grade
            GROUP BY job) t
ON g.job = t.job
WHERE g.score > t.avg_sco;

方案2：where子查询

SELECT g.id,g.job,g.score
FROM grade g
WHERE g.score > (SELECT AVG(score)
                  FROM grade g1
                  WHERE g.job = g1.job
                  GROUP BY job)
ORDER BY id ASC;

方案3：窗口函数

SELECT t.id,t.job,t.score
FROM (SELECT *,AVG(score) OVER(PARTITION BY job) AS avg_sco 
      FROM grade) t
WHERE t.score > t.avg_sco
ORDER BY t.id ASC;