一个 mysql 表，只有学生姓名`name`和学生成绩`grade`两列，现在要 SELECT 出排名前十的同学，但如果满分的有 12 个，则 SELECT 的结果该有 12 个学生，该如何写 SQL 语句？

[Newyorkcity]

谢谢

[Junn]

@reus #38 正解，就是有点错误

with
-- 所有分数
all_grades as (
select distinct grade from grades
)
-- 每个分数及以上的人数
, nums as (
select grade as g, (select count(*) from grades g2 where g2.grade >= g) as num
from all_grades
order by g desc
)
-- 第一个人数等于或者超过 10 的分数线
, i as (
select * from nums
where num >= 10
order by g desc
limit 1
)
-- 分数线以上的所有人
select * from grades
where grade >= (select g from i)
ORDER by grade DESC

[hemingyang]

我靠 当时面试就有人问我一样的 他说先分组,在排序

[gtchan13579]

SELECT `NAME`,`GRADE` FROM 表 WHERE `GRADE` in (SELECT DISTINCT `GRADE` FROM 表 ODER BY `GRADE` DESC LIMIT 10) ODER BY `GRADE`

[gtchan13579]

@gtchan13579 在 mysql 中测试了一下修正一下语法
SELECT `NAME`,`GRADE` FROM test WHERE `GRADE` in (SELECT `GRADE` FROM (SELECT DISTINCT `GRADE` FROM test ORDER BY `GRADE` DESC LIMIT 10)as a) ORDER BY `GRADE` DESC

[winglight2016]

group 是没什么问题的，只是在 group 之后给每个 group 都指定一下名次，这样处理之后取前 10 个 group 就可以了。

这个需求也没有任何问题，现在学校里都是这样处理排名的。

[dogsteve]

#38 #41 的思路 OK 的
给每一个分数一个 rank （所有分数大于这个分数的人数和 + 1 ）取 rank 前十的分数(rank <= 10)。
考虑优化可以先取分数的前十（这个集合肯定是包含所需数据的），然后在这十个分数的人中取分数 rank 前十。

@gtchan13579 这条语句满足不了需求的吧。

[dustinth]

LZ 跑一下我的 SQL 就知道了 , 为什么还在问呢?

[lucybenz]

楼主表达能力实在欠佳，当然也表达清楚了，
需求：
取成绩排名前 10 档的所有学生，成绩相同的算并列排名，不限制总人数;

方案：
1 、按成绩字段 Grade 查询 到排在第 10 档的分数值，设为$ten ；
2 、查询分数大于等于 $ten 的所有数据，并按 Grade 倒叙排列；

实现：
SELECE 'GRADE' FROM ‘table_name'

[lucybenz]

$TEN = SELECT 'GRADE' FROM 'TABLE_NAME' ORDER BY GRADE DESC LIMIT 9,1;

$ARR = SELECT 'GRADE' FROM 'TABLE_NAME' WHERE GRADE >= $TEN ORDER BY GRADE DESC;

[lucybenz]

上方有错❌ 更新

$TEN = SELECT DISTINCT GRADE FROM 'TABLE_NAME' ORDER BY GRADE DESC LIMIT 9,1;

$ARR = SELECT * FROM 'TABLE_NAME' WHERE GRADE >= $TEN ORDER BY GRADE DESC;



SELECT * FROM 'TABLE_NAME' WHERE GRADE >= (SELECT DISTINCT GRADE FROM 'TABLE_NAME' ORDER BY GRADE DESC LIMIT 9,1) ORDER BY GRADE DESC

[lishen226]

以上只有 17 楼是对的，其他的不用看了。
另外再提供一个思路，用两条 SQL+程序判断
select * from student order by grade limit 10
遍历结果集，取出最低分，并将最低分的数据排除掉，然后
select * from student where grade = 最低分
把两个结果集组合起来就是想要的结果

[lishen226]

补充，刚更新的 50 楼也是对的

[alpenstock]

直接判断 dense_rank()大于等于 10 是不是就行了。

[firechat]

要是我就查两遍，第一遍查前十和分数，得到分数后，再查第十名的分数的人

[vone]

@dustinth 看错了，楼主表达能力真的是一言难尽。

[vone]

@reus @alpenstock 更新一下代码，之前没有看清楚描述。

--drop table #SCORE
CREATE TABLE #SCORE
(NAME varchar(32) not null
,GRADE INT not null
)

-- 100 分 7 个 99 分 1 个 98 分 1 个 97 分 6 个
insert #SCORE(NAME,GRADE) values('学生 1',100)
insert #SCORE(NAME,GRADE) values('学生 2',100)
insert #SCORE(NAME,GRADE) values('学生 3',100)
insert #SCORE(NAME,GRADE) values('学生 4',100)
insert #SCORE(NAME,GRADE) values('学生 5',100)
insert #SCORE(NAME,GRADE) values('学生 6',100)
insert #SCORE(NAME,GRADE) values('学生 7',100)
insert #SCORE(NAME,GRADE) values('学生 8',99)
insert #SCORE(NAME,GRADE) values('学生 9',98)
insert #SCORE(NAME,GRADE) values('学生 10',97)
insert #SCORE(NAME,GRADE) values('学生 11',97)
insert #SCORE(NAME,GRADE) values('学生 12',97)
insert #SCORE(NAME,GRADE) values('学生 13',97)
insert #SCORE(NAME,GRADE) values('学生 14',97)
insert #SCORE(NAME,GRADE) values('学生 15',97)
insert #SCORE(NAME,GRADE) values('学生 17',60)
insert #SCORE(NAME,GRADE) values('学生 18',40)
insert #SCORE(NAME,GRADE) values('学生 19',30)

--DROP TABLE #ROW_SCORE
SELECT t.NAME,t.GRADE,ROW_NUMBER() OVER(ORDER BY GRADE desc) ROW,DENSE_RANK() over(order by grade desc ) RANK
INTO #ROW_SCORE
from #SCORE t

SELECT t.NAME,t.GRADE,t.ROW,t.RANK
FROM #ROW_SCORE t
JOIN #ROW_SCORE ten ON ten.ROW=10
where CASE WHEN t.ROW<10 THEN 1
WHEN t.RANK=ten.RANK THEN 1
ELSE 0 END =1

执行结果：

NAME GRADE ROW RANK
学生 1 100 1 1
学生 2 100 2 1
学生 3 100 3 1
学生 4 100 4 1
学生 5 100 5 1
学生 6 100 6 1
学生 7 100 7 1
学生 8 99 8 2
学生 9 98 9 3
学生 10 97 10 4
学生 11 97 11 4
学生 12 97 12 4
学生 13 97 13 4
学生 14 97 14 4
学生 15 97 15 4

[fixU]

select * from a where score >= (select score from aorder by score desc limit 9,1) 这样不行吗

[I2E]

不知所云