V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
Newyorkcity
V2EX  ›  问与答

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

  •  
  •   Newyorkcity · 2020-09-12 09:40:30 +08:00 · 4744 次点击
    这是一个创建于 1294 天前的主题,其中的信息可能已经有所发展或是发生改变。
    谢谢
    第 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
        1
    wangsongyan  
       2020-09-12 09:44:45 +08:00 via iPhone   ❤️ 8
    分数 group 取前十,然后根据分数取学生
    herozzm
        2
    herozzm  
       2020-09-12 09:46:20 +08:00 via Android   ❤️ 2
    单纯 sql 查询解决不了,sql 根据成绩 desc 排序出现所有结果,然后 for 遍历,使用一个 count 变量和 rank 数组变量,每次判断是否 in rank,no in 则 count 加 1,并 append 到 rank,一直到 count 等于 10
    Firewine
        3
    Firewine  
       2020-09-12 09:46:43 +08:00 via Android
    排序 grade,取前 12 个不也是可以吗
    oneisall8955
        4
    oneisall8955  
       2020-09-12 09:52:10 +08:00 via Android
    楼主意思是满分就取全部满分的,否则就取前 10 吧
    Ptu2sha
        5
    Ptu2sha  
       2020-09-12 09:54:45 +08:00
    套个子查询不香?
    swulling
        6
    swulling  
       2020-09-12 09:55:05 +08:00 via iPhone   ❤️ 1
    实际工程使用数据库,不要在数据库上花太多心思,普通查询解决不了的,读出来业务系统二次处理。
    因为成绩相同是小概率事件,先按照成绩 sorted 读 15 个取前十名,如果不够再循环往后读就行了。
    Newyorkcity
        7
    Newyorkcity  
    OP
       2020-09-12 09:55:34 +08:00
    @oneisall8955
    @Firewine
    是的,另外如果满分 100 分 9 个,99 分 2 个,那返回的结果就该是 11 个。但如果满分 9 个,99 分 1 个,98 分 1 个,那返回结果就该是 10 个。。这样的
    799635347
        8
    799635347  
       2020-09-12 09:55:45 +08:00 via iPhone   ❤️ 1
    递归?
    herozzm
        9
    herozzm  
       2020-09-12 09:55:47 +08:00 via Android
    @wangsongyan #1 @Firewine #3 @oneisall8955 #4 楼主的意思排前十名,多个分数一样的算成一个
    Newyorkcity
        10
    Newyorkcity  
    OP
       2020-09-12 09:56:09 +08:00
    @qingdanmo
    @799635347
    可以具体说说吗?
    herozzm
        11
    herozzm  
       2020-09-12 09:57:02 +08:00 via Android
    @Newyorkcity #7 我上面的办法再加入判断逻辑就可以
    qiayue
        12
    qiayue  
       2020-09-12 09:57:27 +08:00
    @Firewine 12 是举例,是一个不确定的数,假如有 20 个满分,则需要取出 20 个。
    herozzm
        13
    herozzm  
       2020-09-12 10:10:04 +08:00
    业务逻辑有问题,如果 100 分有 9 个,99 的有 3 个,你怎么算
    lithiumii
        14
    lithiumii  
       2020-09-12 10:10:38 +08:00 via Android   ❤️ 1
    先找出前 10 的分数,再找人
    gaobing
        15
    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
        16
    herozzm  
       2020-09-12 10:18:09 +08:00   ❤️ 1
    ```伪代码
    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
        17
    corningsun  
       2020-09-12 10:19:13 +08:00 via iPhone   ❤️ 3
    herozzm
        18
    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
        19
    RJH  
       2020-09-12 10:25:03 +08:00
    这个严格来说都不是 SQL 问题,而是业务上遇到 12 个满分的同学时,怎么处理的问题,找产品吧
    herozzm
        20
    herozzm  
       2020-09-12 10:27:19 +08:00
    如果多个分数算一个的逻辑,大致写了一个
    ![伪代码.png]( https://i.loli.net/2020/09/12/MycC2hLYoXBrnVf.png)
    zy445566
        21
    zy445566  
       2020-09-12 10:31:52 +08:00 via Android
    一条不行就用 2 条!
    先查满分人数,如果小于 10 就取前十,大于 10 就取出全部满分
    Firewine
        22
    Firewine  
       2020-09-12 10:36:57 +08:00 via Android
    @qiayue 这样的话,用代码比 SQL 语句更好的处理
    herozzm
        23
    herozzm  
       2020-09-12 10:38:26 +08:00 via Android
    @wangsongyan #1 多次查询性能不是最佳
    mm163
        24
    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
        25
    djj0809  
       2020-09-12 10:51:09 +08:00 via iPhone   ❤️ 7
    先用子查询获得排第十的分数,然后取所有大于等于这个分数的人
    DelayNoMay
        26
    DelayNoMay  
       2020-09-12 10:51:45 +08:00
    查一次再用代码处理呗
    ditel
        27
    ditel  
       2020-09-12 10:53:28 +08:00 via Android
    好像可以用事务来判断
    h123123h
        28
    h123123h  
       2020-09-12 11:07:17 +08:00 via iPhone
    窗口函数
    rodrick
        29
    rodrick  
       2020-09-12 11:08:50 +08:00
    先 orderby 取分再根据分取人正解 最清晰明了 取前十应该数据量本身不算很大吧
    qiayue
        30
    qiayue  
       2020-09-12 11:13:07 +08:00
    不要用一个复杂 SQL 解决复杂需求,有时会多个简单 SQL 不仅耗时更短,还更容易理解
    newtype0092
        31
    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
        32
    herimvane  
       2020-09-12 11:23:07 +08:00 via Android
    窗口函数,rank, 不知道 mysql 支持不
    wangsongyan
        33
    wangsongyan  
       2020-09-12 12:25:27 +08:00 via iPhone
    @herozzm 我提供的方案不满足楼主需求,至于效率问题,用子查询啊
    romisanic
        34
    romisanic  
       2020-09-12 14:31:14 +08:00   ❤️ 1
    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
        35
    vone  
       2020-09-12 15:46:34 +08:00   ❤️ 1
    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
        36
    dustinth  
       2020-09-12 16:11:52 +08:00   ❤️ 1
    假设表结构为 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
        37
    reus  
       2020-09-12 16:31:04 +08:00
    @vone 问题他不是想要 rank 少于 10,而是 count(*) 等于或者刚好超过 10 。应该要用递归 CTE 来做
    reus
        38
    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
        39
    967182  
       2020-09-12 17:03:54 +08:00
    select name,grade,rank() over(order by grade) tt from user_grade;
    sabercoding
        40
    sabercoding  
       2020-09-12 19:17:29 +08:00
    获取 limit10 的成绩,然后再查这些成绩的同学即可。
    Junn
        41
    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
        42
    hemingyang  
       2020-09-12 20:32:08 +08:00
    我靠 当时面试就有人问我一样的 他说先分组,在排序
    gtchan13579
        43
    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
        44
    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
        45
    winglight2016  
       2020-09-12 21:10:42 +08:00
    group 是没什么问题的,只是在 group 之后给每个 group 都指定一下名次,这样处理之后取前 10 个 group 就可以了。

    这个需求也没有任何问题,现在学校里都是这样处理排名的。
    dogsteve
        46
    dogsteve  
       2020-09-12 21:48:05 +08:00
    #38 #41 的思路 OK 的
    给每一个分数一个 rank (所有分数大于这个分数的人数和 + 1 )取 rank 前十的分数(rank <= 10)。
    考虑优化可以先取分数的前十(这个集合肯定是包含所需数据的),然后在这十个分数的人中取分数 rank 前十。

    @gtchan13579 这条语句满足不了需求的吧。
    dustinth
        47
    dustinth  
       2020-09-12 22:45:42 +08:00
    LZ 跑一下我的 SQL 就知道了 , 为什么还在问呢?
    lucybenz
        48
    lucybenz  
       2020-09-13 05:45:58 +08:00
    楼主表达能力实在欠佳,当然也表达清楚了,
    需求:
    取成绩排名前 10 档的所有学生,成绩相同的算并列排名,不限制总人数;

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

    实现:
    SELECE 'GRADE' FROM ‘table_name'
    lucybenz
        49
    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
        50
    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
        51
    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
        52
    lishen226  
       2020-09-13 06:22:49 +08:00
    补充,刚更新的 50 楼也是对的
    alpenstock
        53
    alpenstock  
       2020-09-13 08:10:19 +08:00
    直接判断 dense_rank()大于等于 10 是不是就行了。
    firechat
        54
    firechat  
       2020-09-13 09:09:16 +08:00
    要是我就查两遍,第一遍查前十和分数,得到分数后,再查第十名的分数的人
    vone
        55
    vone  
       2020-09-14 09:21:17 +08:00
    @dustinth 看错了,楼主表达能力真的是一言难尽。
    vone
        56
    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
        57
    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
        58
    I2E  
       2020-09-16 11:07:05 +08:00
    不知所云
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5372 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 35ms · UTC 07:06 · PVG 15:06 · LAX 00:06 · JFK 03:06
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.