【牛客网】SQL练习-蒲公英云

【牛客网】SQL练习

1、考试分数（一）

https://www.nowcoder.com/practice/f41b94b4efce4b76b27dd36433abe398?tpId=82&&tqId=35492&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking

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、考试分数（二）

https://www.nowcoder.com/practice/f456dedf88a64f169aadd648491a27c1?tpId=82&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking

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

采用连接的方式：

select grade.id, grade.job, grade.score
from 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
) t2
on 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、考试分数（三）

https://www.nowcoder.com/practice/b83f8b0e7e934d95a56c24f047260d91?tpId=82&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking

首先，

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