现有学生分数统计表pub_f_class_score如下：

[问答题]

std_nm(学生姓名) class(班级) score(分数) insert_dt(更新时间)

nm1 cls1 98 2018-08-22

nm2 cls2 97 2018-08-22

nm3 cls1 97 2018-08-22

nm4 cls1 95 2018-08-22

nm5 cls2 92 2018-08-22

nm6 cls2 84 2018-08-22

nm7 cls1 91 2018-08-22

nm8 cls2 89 2018-08-22

nm9 cls2 91 2018-08-22

现在需要统计出每个班级中排名前三的学生姓名以及其分数，请写出SQL实现

查看答案及解析

吃了🍄会变大的offer收割机😆

Select std_nm, class, scores

from

(select *, row_number() over(partition by classorder by score desc) as rank from pub_f_class_score) A

where rank<=3;

发表于 2019-04-09 14:54:28 回复(0)

冲上顶峰

select id，name

from table

order by id

limit 2,3

发表于 2019-02-05 16:44:07 回复(0)

单单单单单单

select * from

(select class,

std_nm,

score,

rank() over (partition by class order by score desc) as rank_num

from pub_f_class_score) new

where new.rank_num < 4

发表于 2019-04-07 22:07:26 回复(0)

devil58

SELECT
    std_nm,
    score
FROM
    (
        SELECT
            std_nm,
            score,
            ROW_NUMBER () OVER (
                PARTITION BY CLASS
                ORDER BY
                    score DESC
            ) AS n
        FROM
            pub_f_class_score
    )
WHERE
    n <= 3

发表于 2019-02-13 15:48:44 回复(0)