mysql over 排序_Row_number() OVER(PARTITION BY xxx ORDER BY XXX）分组排序_MySQL

--//创建一个信息表

CREATE TABLE user_student(id decimal(18,0) identity(1,1),st_name nvarchar(30),class nvarchar(10),score decimal(18,2))

--//插入测试数据============start===================

insert into user_student(st_name,class,score)

values('张三','甲','90')

insert into user_student(st_name,class,score)

values('张四','甲','65')

insert into user_student(st_name,class,score)

values('张五','甲','88')

insert into user_student(st_name,class,score)

values('李三','乙','97')

insert into user_student(st_name,class,score)

values('李四','乙','88')

insert into user_student(st_name,class,score)

values('李五','乙','78')

insert into user_student(st_name,class,score)

values('王三','丙','86')

insert into user_student(st_name,class,score)

values('王四','丙','69')

insert into user_student(st_name,class,score)

values('王五','丙','59')

--//插入测试数据============end===================

select * from user_student

--//每个班级分数前两名的学生信息

SELECT ST_NAME,CLASS,SCORE

FROM (

SELECT Row_number() OVER(PARTITION BY CLASS ORDER BY SCORE DESC) AS NUM,*

FROM user_student

) AS T

WHERE NUM<=2

本文原创发布php中文网，转载请注明出处，感谢您的尊重！