mysql实现排名函数三种方式

博客介绍了在没有排名函数的MySQL中实现开窗函数效果的方法。先进行环境搭建并明确目标结果集,接着介绍三种实现方法,即连表查询、子查询和MySQL变量,还对比了它们的执行计划和效率,其中MySQL变量效率最高。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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

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

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

num

id

str

11A
23A
35A
48A
12B
27B
14C
26C

2.实现方法

2.1 连表查询

  1. SELECT
  2.     count(*) num,
  3.     t1.*
  4. FROM
  5.     tem t1
  6.     INNER JOIN tem t2 ON t1.str = t2.str AND t1.id >= t2.id
  7. GROUP BY
  8.     t1.id
  9. ORDER BY
  10.     t1.str, t1.id;

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

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

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

2.2 子查询

  1. SELECT
  2.     (SELECT COUNT(*) FROM tem t2 WHERE t1.str = t2.str AND t1.id >= t2.id) num,
  3.     t1.*
  4. FROM
  5.     tem t1
  6. ORDER BY
  7.     t1.str, t1.id;

再看看执行计划

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

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

2.3MySQL变量

  1. SELECT
  2.     @num := IF(@str = str, @num + 1, 1) num,
  3.     id,
  4.     @str := str str
  5. FROM
  6.     tem, (SELECT @str := '', @num := 0) t1
  7. ORDER BY
  8.     str, id;

执行计划

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

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

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值