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

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 1531 天前的主题，其中的信息可能已经有所发展或是发生改变。

谢谢

第 1 条附言 · 2020-09-12 10:48:51 +08:00

不仅要考虑满分的情况。。准确地说，同一个分数有 n 个人，不论 n 为多大，都只视为 1 个人。

所以满分 100 分，分数都是自然数的话：

12 个满分，正确的返回结果应该有 12 个同学。
9 个满分，2 个 99 分，正确的返回结果应该有 11 个同学。
8 个满分，2 个 99 分，1 个 98 分，正确的返回结果应该有 10 个同学，那个 98 分的同学被不在结果内。
8 个满分，1 个 99 分，2 个 98 分，正确的返回结果应该有 11 个同学，两个 98 分的同学全部在结果内。

谢谢。。。

第 2 条附言 · 2020-09-12 10:49:57 +08:00

第一条附言中 “准确地说，同一个分数有 n 个人，不论 n 为多大，都只视为 1 个人。”

这句话是错的，还是请直接看我下面举的例子吧。。

第 3 条附言 · 2020-09-12 22:37:53 +08:00

各位，如果排名前十的成绩分别是 100 分 99 分 98 分 97 分 …… 91 分
但 100 分有 8 个 99 分有 1 个 98 分有 2 个
那么想要的结果中有 11 个学生

先按成绩分组取前十再 SELECT 出学生的思路里，如何知道前十的学生是十一个人？

而如果 100 分 7 个 99 分 1 个 98 分 1 个 97 分 6ge

那结果中又应当有 15 个学生

如何知道前十的学生是 15 个人？

当然，取出结果集后让 java 或者什么业务层来干是很轻易的了。。就是不知道纯 SQL 做得到吗？

58 条回复 • 2020-09-16 11:07:05 +08:00

wangsongyan

2020-09-12 09:44:45 +08:00 via iPhone

分数 group 取前十，然后根据分数取学生

herozzm

2020-09-12 09:46:20 +08:00 via Android

单纯 sql 查询解决不了，sql 根据成绩 desc 排序出现所有结果，然后 for 遍历，使用一个 count 变量和 rank 数组变量，每次判断是否 in rank，no in 则 count 加 1，并 append 到 rank，一直到 count 等于 10

Firewine

2020-09-12 09:46:43 +08:00 via Android

排序 grade，取前 12 个不也是可以吗

oneisall8955

2020-09-12 09:52:10 +08:00 via Android

楼主意思是满分就取全部满分的，否则就取前 10 吧

Ptu2sha

2020-09-12 09:54:45 +08:00

套个子查询不香？

swulling

2020-09-12 09:55:05 +08:00 via iPhone

实际工程使用数据库，不要在数据库上花太多心思，普通查询解决不了的，读出来业务系统二次处理。
因为成绩相同是小概率事件，先按照成绩 sorted 读 15 个取前十名，如果不够再循环往后读就行了。

Newyorkcity

2020-09-12 09:55:34 +08:00

@oneisall8955
@Firewine
是的，另外如果满分 100 分 9 个，99 分 2 个，那返回的结果就该是 11 个。但如果满分 9 个，99 分 1 个，98 分 1 个，那返回结果就该是 10 个。。这样的

799635347

2020-09-12 09:55:45 +08:00 via iPhone

递归？

herozzm

2020-09-12 09:55:47 +08:00 via Android

@wangsongyan #1 @Firewine #3 @oneisall8955 #4 楼主的意思排前十名，多个分数一样的算成一个

Newyorkcity

2020-09-12 09:56:09 +08:00

@qingdanmo
@799635347
可以具体说说吗？

herozzm

2020-09-12 09:57:02 +08:00 via Android

@Newyorkcity #7 我上面的办法再加入判断逻辑就可以

qiayue

2020-09-12 09:57:27 +08:00

@Firewine 12 是举例，是一个不确定的数，假如有 20 个满分，则需要取出 20 个。

herozzm

2020-09-12 10:10:04 +08:00

业务逻辑有问题，如果 100 分有 9 个，99 的有 3 个，你怎么算

lithiumii

2020-09-12 10:10:38 +08:00 via Android

先找出前 10 的分数，再找人

gaobing

2020-09-12 10:12:39 +08:00 via Android

select name , grade
from s
where grade in
(select grade from s order by grade desc limit 0,10)

herozzm

2020-09-12 10:18:09 +08:00

```伪代码
students = "select * from table ORDER BY grade"

count = 0;
ranks = [];

for student in students {

if count == 10 {
break;
}

if student not in ranks {
ranks = append(ranks, student)
count++
}

}

print(ranks)

corningsun

2020-09-12 10:19:13 +08:00 via iPhone

这个写的就很详细了

https://blog.csdn.net/delongcpp/article/details/107732343

herozzm

2020-09-12 10:21:30 +08:00

上面打错了部分代码，这个伪代码
```
students = "select * from table ORDER BY grade"

count = 0;
ranks = [];

for student in students {

if count == 10 {
break;
}

for rank in ranks {
if student.grade == rank.grade {
ranks = append(ranks, student)
continue;
}
}

ranks = append(ranks, student)
count++

}

print(ranks)

RJH

2020-09-12 10:25:03 +08:00

这个严格来说都不是 SQL 问题，而是业务上遇到 12 个满分的同学时，怎么处理的问题，找产品吧

herozzm

2020-09-12 10:27:19 +08:00

如果多个分数算一个的逻辑，大致写了一个
![伪代码.png]( https://i.loli.net/2020/09/12/MycC2hLYoXBrnVf.png)

zy445566

2020-09-12 10:31:52 +08:00 via Android

一条不行就用 2 条！
先查满分人数，如果小于 10 就取前十，大于 10 就取出全部满分

Firewine

2020-09-12 10:36:57 +08:00 via Android

@qiayue 这样的话，用代码比 SQL 语句更好的处理

herozzm

2020-09-12 10:38:26 +08:00 via Android

@wangsongyan #1 多次查询性能不是最佳

mm163

2020-09-12 10:43:54 +08:00

select * from tts where grade = 100
UNION DISTINCT
(select * from tts order by grade desc limit 10)

djj0809

2020-09-12 10:51:09 +08:00 via iPhone

先用子查询获得排第十的分数，然后取所有大于等于这个分数的人

DelayNoMay

2020-09-12 10:51:45 +08:00

查一次再用代码处理呗

ditel

2020-09-12 10:53:28 +08:00 via Android

好像可以用事务来判断

h123123h

2020-09-12 11:07:17 +08:00 via iPhone

窗口函数

rodrick

2020-09-12 11:08:50 +08:00

先 orderby 取分再根据分取人正解最清晰明了取前十应该数据量本身不算很大吧

qiayue

2020-09-12 11:13:07 +08:00

不要用一个复杂 SQL 解决复杂需求，有时会多个简单 SQL 不仅耗时更短，还更容易理解

newtype0092

2020-09-12 11:13:30 +08:00

窗口函数就是干这个事的
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_dense-rank
看最后一个 rank 的例子

herimvane

2020-09-12 11:23:07 +08:00 via Android

窗口函数，rank, 不知道 mysql 支持不

wangsongyan

2020-09-12 12:25:27 +08:00 via iPhone

@herozzm 我提供的方案不满足楼主需求，至于效率问题，用子查询啊

romisanic

2020-09-12 14:31:14 +08:00

select * from table where grade in (select t.grade from ( select grade , count(name) from table group by grade order by grade desc limit 10 ) t)

逻辑就是其实你想要的效果就是按照已经出现的分数排名，想要前十名分数的所有同学，对吧

vone

2020-09-12 15:46:34 +08:00

DENSE_RANK() 可以完美解决。

DENSE_RANK() ：
此函数返回结果集分区中每行的排名，排名值没有间断。特定行的排名等于该特定行之前不同排名值的数量加一。
如果两个或更多行在同一分区中具有相同的排名值，那么每个行将获得相同的排名。例如，如果两位顶尖销售员具有相同的 SalesYTD 值，则他们的排名值都为一。接下来 SalesYTD 最高的销售人员排名值为二。这比所讨论的行之前的不同行的数量多了一。因此，DENSE_RANK 函数返回的数字没有间断，并且始终具有连续的排名值。

https://docs.microsoft.com/zh-cn/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-ver15

以下是完整测试代码：
--drop table #SCORE
CREATE TABLE #SCORE
(NAME varchar(32) not null
,GRADE INT not null
)
insert #SCORE(NAME,GRADE) values('学生 1',cast(rand()*100 as int))
insert #SCORE(NAME,GRADE) values('学生 2',cast(rand()*100 as int))
insert #SCORE(NAME,GRADE) values('学生 3',95)
insert #SCORE(NAME,GRADE) values('学生 4',cast(rand()*100 as int))
insert #SCORE(NAME,GRADE) values('学生 5',cast(rand()*100 as int))
insert #SCORE(NAME,GRADE) values('学生 6',95)
insert #SCORE(NAME,GRADE) values('学生 7',cast(rand()*100 as int))
insert #SCORE(NAME,GRADE) values('学生 8',cast(rand()*100 as int))
insert #SCORE(NAME,GRADE) values('学生 9',cast(rand()*100 as int))
insert #SCORE(NAME,GRADE) values('学生 10',80)
insert #SCORE(NAME,GRADE) values('学生 12',cast(rand()*100 as int))
insert #SCORE(NAME,GRADE) values('学生 13',cast(rand()*100 as int))
insert #SCORE(NAME,GRADE) values('学生 14',cast(rand()*100 as int))
insert #SCORE(NAME,GRADE) values('学生 15',80)
insert #SCORE(NAME,GRADE) values('学生 16',cast(rand()*100 as int))
insert #SCORE(NAME,GRADE) values('学生 17',cast(rand()*100 as int))

select * from (
select t.NAME,t.GRADE,DENSE_RANK() over(order by grade desc ) RANK
from #SCORE t
) t
where t.RANK<=10

dustinth

2020-09-12 16:11:52 +08:00

假设表结构为 student (id, name, grade).
完整不考虑效率的 sql

select f.id as id, f.name as name, f.grade as grade from student as f where f.grade >=
(select max(e.grade) as cutgrade from
(select sum(d.acount) as count, d.bgrade as grade from
(select a.cnt as acount, a.grade as agrade, b.grade as bgrade from
(select count(id) as cnt, grade from student group by grade order by grade desc) as a join
(select c.grade as grade from (select distinct grade as grade from student order by grade desc) as c) as b
where a.grade >= b.grade
order by bgrade desc) as d group by grade order by grade desc) as e where e.count >= 10)

reus

2020-09-12 16:31:04 +08:00

@vone 问题他不是想要 rank 少于 10，而是 count(*) 等于或者刚好超过 10 。应该要用递归 CTE 来做

reus

2020-09-12 16:42:04 +08:00

对于每个分数，统计该分数线以上的人数，然后找出第一个人数等于或者超过 10 的分数线，再根据分数线筛选即可

精要是统计“分数线以上”的人数，而不是单一分数的人数

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

967182

2020-09-12 17:03:54 +08:00

select name,grade,rank() over(order by grade) tt from user_grade;

sabercoding

2020-09-12 19:17:29 +08:00

获取 limit10 的成绩，然后再查这些成绩的同学即可。

Junn

2020-09-12 20:08:09 +08:00

@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

2020-09-12 20:32:08 +08:00

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

gtchan13579

2020-09-12 20:40:21 +08:00

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

gtchan13579

2020-09-12 20:56:42 +08:00

@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

2020-09-12 21:10:42 +08:00

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

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

dogsteve

2020-09-12 21:48:05 +08:00

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

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

dustinth

2020-09-12 22:45:42 +08:00

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

lucybenz

2020-09-13 05:45:58 +08:00

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

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

实现：
SELECE 'GRADE' FROM ‘table_name'

lucybenz

2020-09-13 05:52:09 +08:00

$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

2020-09-13 06:10:05 +08:00

上方有错❌ 更新

$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

2020-09-13 06:10:10 +08:00

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

lishen226

2020-09-13 06:22:49 +08:00

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

alpenstock

2020-09-13 08:10:19 +08:00

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

firechat

2020-09-13 09:09:16 +08:00

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

vone

2020-09-14 09:21:17 +08:00

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

vone

2020-09-14 09:36:03 +08:00

@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

2020-09-15 14:08:18 +08:00

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

I2E

2020-09-16 11:07:05 +08:00

不知所云