面試關於 MySQL 的編寫

_zzh發表於2019-06-10
面試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;

相關文章