[转载]面试关于 MySQL 的编写

上段时间去面试的时候,原本以为自己 SQL 写的应该没什么大问题的。但是看到面试题的时候却懵逼了,回家自己捣鼓了一下,发现自己还是个弟弟。不得不说 SQL 博大精深?总得来说还是 group by 理解的不够深刻。

下面是数据表:
[转载]面试关于 MySQL 的编写

1. 请写出每门科目成绩前三的数据。(表:student_score,姓名:name,科目:subject,分数 score)

SELECT
    a.*
FROM
    student AS a
LEFT JOIN student AS b ON a.`subject` = b.`subject`
AND a.score < b.score
GROUP BY
    a.id,
    a. SUBJECT,
    a.score
HAVING
    COUNT(b.id) < 3
ORDER BY
    a.`subject`,
    a.score DESC

    参考链接:https://blog.csdn.net/sjw_peak/article/details/88567772

2. 写出删除表中重复数据,并保留一条。

DELETE FROM student WHERE
(`name`,`subject`,score) IN (
    SELECT t.name,t.subject,t.score FROM (
        SELECT `name`,`subject`,score FROM student 
        GROUP BY `name`,`subject`,score 
        HAVING COUNT(1)>1
    )t
)
AND id not in(
  SELECT a.minId FROM (
        SELECT id as minId FROM student 
        GROUP BY `name`,`subject`,score 
        HAVING COUNT(1)>1
    )a
)

参考链接:https://blog.csdn.net/n950814abc/article/details/82284838

3. 写出所有科目成绩都大于 80 分的学生数据

select name from student group by name having min(score)>80;

本文章转载自: https://learnku.com/articles/29467

未经允许不得转载:www.ym68.cc Boring Site » [转载]面试关于 MySQL 的编写
分享到:
赞(0) 打赏

评论抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

欢迎访问本网站!

去投稿

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏