mysql实现排名函数三种方式

MySQL没有提供排名函数，但是我们可以通过一些技巧来实现开窗函数的效果。

1. 环境搭建、目标结果集

CREATE TABLE `tem` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `str` char(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
 
 
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (1, 'A');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (2, 'B');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (3, 'A');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (4, 'C');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (5, 'A');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (6, 'C');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (7, 'B');
INSERT INTO `test`.`tem`(`id`, `str`) VALUES (8, 'A');

num	id	str
1	1	A
2	3	A
3	5	A
4	8	A
1	2	B
2	7	B
1	4	C
2	6	C

2.实现方法

2.1 连表查询

SELECT
    count(*) num,
    t1.*
FROM
    tem t1
    INNER JOIN tem t2 ON t1.str = t2.str AND t1.id >= t2.id
GROUP BY
    t1.id
ORDER BY
    t1.str, t1.id;

让我们看看这个查询的执行计划

+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                           |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
|  1 | SIMPLE      | t1    | ALL  | PRIMARY       | NULL | NULL    | NULL |    8 | Using temporary; Using filesort                 |
|  1 | SIMPLE      | t2    | ALL  | PRIMARY       | NULL | NULL    | NULL |    8 | Using where; Using join buffer (flat, BNL join) |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+
2 rows in set (0.06 sec)

非常低效：临时表、文件排序、循环嵌套都用上了

2.2 子查询

SELECT
    (SELECT COUNT(*) FROM tem t2 WHERE t1.str = t2.str AND t1.id >= t2.id) num,
    t1.*
FROM
    tem t1
ORDER BY
    t1.str, t1.id;

再看看执行计划

+----+--------------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using filesort |
|  2 | DEPENDENT SUBQUERY | t2    | ALL  | PRIMARY       | NULL | NULL    | NULL |    8 | Using where    |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------+
2 rows in set (0.06 sec)

很好，两次全扫描，而且只用到了文件排序，如果加上索引，文件排序也可以避免。

2.3MySQL变量

SELECT
    @num := IF(@str = str, @num + 1, 1) num,
    id,
    @str := str str
FROM
    tem, (SELECT @str := '', @num := 0) t1
ORDER BY
    str, id;

执行计划

+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |                |
|  1 | PRIMARY     | tem        | ALL    | NULL          | NULL | NULL    | NULL |    8 | Using filesort |
|  2 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL | No tables used |
+----+-------------+------------+--------+---------------+------+---------+------+------+----------------+
3 rows in set (0.06 sec)

效率最高，一次全扫描搞定。文件排序是因为没有索引。