mysql组内排序并标记行数

一.适用于mysql8以上

表结构

查询语句，注意不是用group by

SELECT
	row_number() over ( PARTITION BY patient_name ORDER BY test_time DESC ) rn,
	a.* 
FROM
	test a

下面是查询结果

DENSE_RANK() partition        order by  重复排序
RANK()      partition        order by 重复排序，跳过一个
row_number  partition  唯一 

二.适用于mysql5

1.1个分组依据

需求示例：现在需要统计学生的各科成绩排名，就需要安装subject科目进行分组，然后按分数倒序排序。
有一个学生表如下：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
  `name` varchar(5) NOT NULL COMMENT '学生姓名',
  `subject` varchar(6) DEFAULT NULL COMMENT '科目',
  `score` smallint(3) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

表里面的数据如下：
id name subject score
1 张三 语文 77
2 李四 语文 67
3 王五 语文 85
4 张三 数学 82
5 李四 数学 67
6 王五 英语 85
7 王五 数学 85

#SUBJECT作为group by的字段，score作为组内排序的字段
SELECT a.NAME, a.score, a.SUBJECT, @last :=IF(@FIRST = a.SUBJECT, @last + 1, 1 ) AS rn, @FIRST := a.SUBJECT 
FROM student a, ( SELECT @last := 0, @FIRST := NULL ) b 
ORDER BY a.SUBJECT, a.score DESC

结果：

 

SQL解释：

order by 后面必须是a.SUBJECT, a.score DESC。 因为先按a.SUBJECT排序，SUBJECT相同再按score字段排序。
2.if语句表示，把subject的值赋给变量@FIRST,如果@FIRST值相同，则@last = @last +1，如果不同则@last = 1。然后@FISRST = SUBJECT字段。这样就能利用变量来实现相同的SUBJECT序号自动加一。
3.查询条件中的@last 语句一定要在@FIRST语句的前面。因为第一次查询的时候第一个@FISRT = NULL, 就会设置@last= 1,然后设置@FIRST = 数学，然后第二次查询的时候@FIRST与数学相等，然后@last= @last + 1；如此类推。如果顺序调换，@FIRST字段先赋值为数学，然后再判断@last :=IF(@FIRST = a.SUBJECT, @last + 1, 1 ) 的@FIRST = a.SUBJECT条件成立 就会导致@last总是=@last + 1。最终的结果就导致组内分组排序失败。

2.2个分组依据

现在多插入几条数据，每个学生同一个科目考了多次，有多个成绩。需要按学生和科目进行分组。

SELECT a.NAME,  a.SUBJECT, a.score,  @last :=IF(@FIRST = a.name and @SECOND =a.SUBJECT, @last + 1, 1 ) AS rn,@FIRST := a.NAME, @SECOND :=a.SUBJECT
FROM student a, ( SELECT @last := 0, @FIRST := NULL,@SECOND := NULL ) b 
ORDER BY a.NAME, a.SUBJECT, a.score DESC

 和一个分组的sql类似，需要多加一个变量即可。
但有时候( SELECT @last := 0, @FIRST := NULL,@SECOND := NULL ) b 这里面的@SECOND := NULL可能出现漏写的情况，写成了( SELECT @last := 0, @FIRST := NULL) b 就会导致第一次查询的时候序号全部都是1。这时候因为@SECOND未定义，IF(@FIRST = a.name and @SECOND =a.SUBJECT, @last + 1, 1 )这句在执行的时候总是 @last := 1 执行。但是第二次执行这个sql却发现是正确的，这是因为在当前连接中的第一次查询已经使用了@SECOND变量。