本文共 963 字，大约阅读时间需要 3 分钟。

1、考试分数（一）

select job, ROUND(AVG(score)*1.0, 3) `avg` from grade group by job order by `avg` desc;select job, round(avg(score), 3) as avg_score from grade group by job order by avg_score desc;

2、考试分数（二）

题目有误，应该是“查找分数大于其所在工作组的平均分的用户”

采用连接的方式：

select grade.id, grade.job, grade.scorefrom grade, (select job, avg(score) as avg_score from grade group by job) as temp where grade.job = temp.job and grade.score > temp.avg_score order by grade.id;select t1.* from grade t1 INNER JOIN (    select job, round(avg(score)*1.0, 3) `avg` from grade group by job) t2on t1.job = t2.job and t1.score > t2.`avg` order by t1.id;

采用子查询的方式：

select x.id, x.job, x.score from grade as x where x.score > (    select avg(y.score) from grade as y where y.job = x.job group by y.job)order by x.id asc;

3、考试分数（三）

首先，

select t1.id from grade as t1 join grade as t2 on t1.language_id = t2.language_id and t1.socre <= t2.scoregroup by t1.language_id, t1.id having count(distinct t2.score) < 3;

转载地址：http://efnii.baihongyu.com/