我有一張桌子，里面有不同的人，他們的成績不同。我需要找到成績最高的兩個人。如果有任何關系，也請選擇它們。我現在唯一得到的是這個查詢的第一高分：
SELECT name,
MAX(grade) AS max_grade
FROM exercise_5
GROUP BY name
HAVING max_grade = ( SELECT MAX(grade) as max_grade
FROM exercise_5
GROUP BY name
ORDER BY max_grade DESC LIMIT 1
);
有誰知道它是怎么做的？
uj5u.com熱心網友回復：
SELECT grades.*
FROM (
SELECT name, MAX(grade) as max_grade
FROM exercise_5
GROUP BY 1
) grades
NATURAL JOIN (
SELECT DISTINCT MAX(grade) as max_grade
FROM exercise_5
GROUP BY name
ORDER BY max_grade DESC LIMIT 2
) max_grades
ORDER BY 2 DESC, 1 ASC;
uj5u.com熱心網友回復：
使用此代碼（此代碼無法顯示平局）
with CTE AS (SELECT *,ROW_NUMBER() OVER(ORDER BY grade DESC) AS rn FROM Notes) SELECT name, grade FROM CTE WHERE rn IN (1,2);
使用這個來顯示關系：
with CTE AS (SELECT *,RANK() OVER(ORDER BY grade DESC) AS rn FROM Notes) SELECT name, grade FROM CTE WHERE rn IN (1,2);
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/521033.html
標籤：mysql子查询最大限度
上一篇：如果傳遞的引數之一為空，如何使存盤程序選擇所有內容？
下一篇：MySQL為查詢結果轉置行中的列