求教SQL面試題目：單張表查詢某欄位排在第二或第n-1問題？

最近面試遇到一個SQL問題，到現在自己沒解決出來，求教。題目如下：

表結構如圖：

問題如下：

問題一：查詢數學課程成績最高的學生記錄？

問題二：查詢數學課程成績排在第二的學生記錄？

問題一的解答相對簡單，我是寫出來了，我的寫法是

select * from student_score where score =(select MAX(score) from student_score s where s.course="數學") and course = "數學";或
select * from student_score s where s.score in (select MAX(s1.score) from student_score s1 where s1.course = s.course ) and s.course = "數學";


但是問題2，很慚愧啊，到現在還沒寫出來，麻煩高手寫幾個，給小弟學習學習，在此感謝！

---

給題主提出表揚，並且希望知乎上水平比較低的題主向本題的題主學習。

題主寫出了自己的代碼。

如果沒有這2行代碼的話，會被我當成伸手黨直接拉黑了。

下面正經回答問題：

問題一：

SELECT * FROM student_score
WHERE course="數學"
ORDER BY score DESC
LIMIT 1


問題二：

SELECT * FROM student_score
WHERE course="數學"
ORDER BY score DESC
LIMIT 1 OFFSET 1


老版本上不支持OFFSET的寫法：

SELECT * FROM student_score
WHERE course="數學"
ORDER BY score DESC
LIMIT 1, 1


---

樓主你對於第一個問題的回答是正確的。

@劉項 的兩個回答都是錯的，因為沒有考慮到有可能兩個人會有相同的分數。

對於第二個問題應該這樣解決：

select *
from student_score
where course = "數學"
and score = (
select distinct score
from student_score
where course = "數學"
order by score desc
limit 1, 1
)


有沒有發現思路和樓主自己解決第一個問題的思路很類似。

---

借鑒了stackoverflow的最高票答案：puzzle - What is the simplest SQL Query to find the second largest value?

之前答案忘了course=數學這一限定條件。謝謝 @王潤中 指正

SELECT * FROM student_score
WHERE score=(SELECT MAX(score)
FROM student_score
WHERE score&< SELECT MAX(score) FROM student_score WHERE course="數學" AND course="數學")

---

菜鳥剛學習，試著做了一下：

第一個：SELECT Top 1 *

FROM

(

SELECT * FROM CHENGJI11 WHERE course="數學"

)d

ORDER BY SCORE DESC

第二個：

SELECT *

FROM

(

SELECT ROW_NUMBER()OVER(PARTITION BY COURSE ORDER BY SCORE DESC) XUHAO,

*

FROM CHENGJI11

) F

WHERE XUHAO=2 AND course="數學"

---

有幾位朋友已經給過答案了，我也在學習sql過程中，嘗試一下，用了窗口函數，不同窗口函數排序規則不一樣，我想樓主應該是dense_rank()這種排序規則；

select id,name,course,score
from(
select id,name,course,score
,row_number() over (partition by course order by score desc ) as row_number
,rank() over (partition by course order by score desc ) as rank
,dense_rank() over (partition by course order by score desc ) as dense_rank
from temp_mobile.sql_exc
)a
where course="數學" and dense_rank=1 --這裡改幾就可以提取第幾名的信息


---

第一種方法：排序後篩選

第二種方法：

1、查出最高分的記錄

2、查出排除最高分記錄剩餘記錄的最高分 score

3、查出分值等於score的記錄

---

試試新寫法也不知道可不可以

select * from (
select id,name,course,score,rownumber()over(partition by course order by score desc) as rank from student_score )
where course="數學" and rank=2


---

第二問

第一步先查出前2的記錄

第二步在第一步的結果里查出正序的第一條就是了。

如果要查排名第N的記錄，只需要修改第一步里的2。

---

select * from student_score where course="數學" order by score desc limit 1,1

沒在電腦試過不知道有沒有問題，歡迎指教

另外推薦w3resource練習mysql，各位要是有各種mysql題目熱烈歡迎討論，性能優化什麼的就算了。。。

至於同分的問題的話，容我想想。。

---

你查查關鍵字limit、order by

PS：我也是新手

---

推薦閱讀：