面试关于 MySQL 的编写

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

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;

本作品采用《CC 协议》，转载必须注明作者和本文链接