Newyorkcity
V2EX  ›  问与答

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

  •  
  •   Newyorkcity · Sep 12, 2020 · 6630 views
    This topic created in 2257 days ago, the information mentioned may be changed or developed.
    谢谢
    Supplement 1  ·  Sep 12, 2020
    不仅要考虑满分的情况。。准确地说,同一个分数有 n 个人,不论 n 为多大,都只视为 1 个人。

    所以满分 100 分,分数都是自然数的话:

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

    谢谢。。。
    Supplement 2  ·  Sep 12, 2020
    第一条附言中 “准确地说,同一个分数有 n 个人,不论 n 为多大,都只视为 1 个人。”

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

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

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

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

    实现:
    SELECE 'GRADE' FROM ‘table_name'
    benzlucy
    benzlucy
    Sep 13, 2020
    49
    $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;
    benzlucy
    benzlucy
    Sep 13, 2020
    50
    上方有错❌ 更新

    $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
    lishen226
    Sep 13, 2020
    51
    以上只有 17 楼是对的,其他的不用看了。
    另外再提供一个思路,用两条 SQL+程序判断
    select * from student order by grade limit 10
    遍历结果集,取出最低分,并将最低分的数据排除掉,然后
    select * from student where grade = 最低分
    把两个结果集组合起来就是想要的结果
    lishen226
    lishen226
    Sep 13, 2020
    52
    补充,刚更新的 50 楼也是对的
    alpenstock
    alpenstock
    Sep 13, 2020
    53
    直接判断 dense_rank()大于等于 10 是不是就行了。
    firechat
    firechat
    Sep 13, 2020
    54
    要是我就查两遍,第一遍查前十和分数,得到分数后,再查第十名的分数的人
    vone
    vone
    Sep 14, 2020
    55
    @dustinth 看错了,楼主表达能力真的是一言难尽。
    vone
    vone
    Sep 14, 2020
    56
    @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
    fixU
    Sep 15, 2020
    57
    select * from a where score >= (select score from aorder by score desc limit 9,1) 这样不行吗
    I2E
    I2E
    Sep 16, 2020
    58
    不知所云
    About   ·   Help   ·   Advertise   ·   Blog   ·   API   ·   FAQ   ·   Privacy   ·   Solana   ·   4649 Online   Highest 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 161ms · UTC 06:20 · PVG 14:20 · LAX 22:20 · JFK 01:20
    ♥ Do have faith in what you're doing.