【牛客网】SQL练习

痛定思痛。 2022-12-08 15:45 13阅读 0赞

1、考试分数（一）

[https://www.nowcoder.com/practice/f41b94b4efce4b76b27dd36433abe398?tpId=82&&tqId=35492&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking][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][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][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;

[https_www.nowcoder.com_practice_f41b94b4efce4b76b27dd36433abe398_tpId_82_tqId_35492_rp_1_ru_ta_sql_qru_ta_sql_question-ranking]: https://www.nowcoder.com/practice/f41b94b4efce4b76b27dd36433abe398?tpId=82&&tqId=35492&rp=1&ru=/ta/sql&qru=/ta/sql/question-ranking
[https_www.nowcoder.com_practice_f456dedf88a64f169aadd648491a27c1_tpId_82_rp_1_ru_2Fta_2Fsql_qru_2Fta_2Fsql_2Fquestion-ranking]: https://www.nowcoder.com/practice/f456dedf88a64f169aadd648491a27c1?tpId=82&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
[https_www.nowcoder.com_practice_b83f8b0e7e934d95a56c24f047260d91_tpId_82_rp_1_ru_2Fta_2Fsql_qru_2Fta_2Fsql_2Fquestion-ranking]: https://www.nowcoder.com/practice/b83f8b0e7e934d95a56c24f047260d91?tpId=82&rp=1&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking