面试关于 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 协议》,转载必须注明作者和本文链接
推荐文章: