MySQL 分组排序后加编号
求:个个班学生成绩排名
表:
CREATE TABLE test_user
(
id
int(11) NOT NULL AUTO_INCREMENT COMMENT ‘id’,
name
varchar(32) DEFAULT NULL COMMENT ‘姓名’,
class
varchar(32) DEFAULT NULL COMMENT ‘班级’,
grade
int(11) DEFAULT NULL COMMENT ‘成绩’,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COMMENT=‘测试-用户表’;
表图:
结果图:
新的改变
rowNum:是序号也排序;
rank :在班级的排名,但是又重复分数时,排名会有问题
Sql 语句:
SELECT
@i :=
CASE
WHEN @class = t.class THEN
@i + 1 ELSE 1
END AS rowNum,
@class := t.class AS class,
t.*
FROM
(
SELECT
a.*,
count( 1 ) AS rank
FROM
test_user a
LEFT JOIN test_user b ON a.class = b.class
AND a.grade <= b.grade
WHERE
1 = 1
GROUP BY
a.class,
a.NAME
ORDER BY
a.class,
rank -- order by a.class,a.name
) t,
( SELECT @i := 0, @class := '' ) AS a