对于MySQL分组取前N条记录的解释

之前遇到一个朋友问到我的一个问题：如何用一条sql取出分组中的排名前N条数据。不太会，然后查询了一些文章，例如这一篇https://blog.csdn.net/yige9394/article/details/79481706，算是写的非常详细了，但是这篇文章并没有解释以下sql为什么能够取出每个品类中价格最高的两个商品：

SELECT a.*FROM mygoods a WHERE 
(SELECT count(*) FROM mygoods WHERE cat_id=a.cat_id AND price> a.price AND STATUS=1)< 2 
AND STATUS=1 ORDER BY a.cat_id,a.price DESC;

有可能是作者认为sql语句比较简单容易理解吧，但是我想通这个sql也是用了一些时间的，我把我自己的理解写下来。

 

首先我建了一张表，post_message，这是假设这是用户留言信息表吧。

DROP TABLE IF EXISTS `post_message`;
CREATE TABLE `post_message`  (
  `id` int(255) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `category_id` int(10) NULL DEFAULT NULL COMMENT '分类id',
  `create_time` int(10) NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of post_message
-- ----------------------------
INSERT INTO `post_message` VALUES (1, 1, 1551440566);
INSERT INTO `post_message` VALUES (2, 1, 1551440567);
INSERT INTO `post_message` VALUES (3, 1, 1551440568);
INSERT INTO `post_message` VALUES (4, 2, 1551440569);
INSERT INTO `post_message` VALUES (5, 2, 1551440560);
INSERT INTO `post_message` VALUES (6, 2, 1551440561);
INSERT INTO `post_message` VALUES (7, 3, 1551440562);
INSERT INTO `post_message` VALUES (8, 3, 1551440563);
INSERT INTO `post_message` VALUES (9, 3, 1551440564);

SET FOREIGN_KEY_CHECKS = 1;

目前的数据看起来结果如下：

mysql> select * from post_message;
+----+-------------+-------------+
| id | category_id | create_time |
+----+-------------+-------------+
|  1 |           1 |  1551440566 |
|  2 |           1 |  1551440567 |
|  3 |           1 |  1551440568 |
|  4 |           2 |  1551440569 |
|  5 |           2 |  1551440560 |
|  6 |           2 |  1551440561 |
|  7 |           3 |  1551440562 |
|  8 |           3 |  1551440563 |
|  9 |           3 |  1551440564 |
+----+-------------+-------------+
9 rows in set (0.00 sec)

OK，我们先写出最终查询出每个分类中，创建时间最大的两条记录（create_time最大）的完整sql：

mysql> select a.* from post_message a where 1 >= (select count(*) from post_message where a.category_id = category_id and create_time > a.create_time);
+----+-------------+-------------+
| id | category_id | create_time |
+----+-------------+-------------+
|  2 |           1 |  1551440567 |
|  3 |           1 |  1551440568 |
|  4 |           2 |  1551440569 |
|  6 |           2 |  1551440561 |
|  8 |           3 |  1551440563 |
|  9 |           3 |  1551440564 |
+----+-------------+-------------+
6 rows in set (0.00 sec)

直接看的话可能不是很好理解，那么我们可以看如下的简单一点的sql：

mysql> select a.*, b.*  from post_message a, post_message b where a.category_id = b.category_id and a.create_time > b.create_time and a.id = 1;
Empty set (0.00 sec)

mysql> select a.*, b.*  from post_message a, post_message b where a.category_id = b.category_id and a.create_time > b.create_time and a.id = 2;
+----+-------------+-------------+----+-------------+-------------+
| id | category_id | create_time | id | category_id | create_time |
+----+-------------+-------------+----+-------------+-------------+
|  2 |           1 |  1551440567 |  1 |           1 |  1551440566 |
+----+-------------+-------------+----+-------------+-------------+
1 row in set (0.00 sec)

mysql> select a.*, b.*  from post_message a, post_message b where a.category_id = b.category_id and a.create_time > b.create_time and a.id = 3;
+----+-------------+-------------+----+-------------+-------------+
| id | category_id | create_time | id | category_id | create_time |
+----+-------------+-------------+----+-------------+-------------+
|  3 |           1 |  1551440568 |  1 |           1 |  1551440566 |
|  3 |           1 |  1551440568 |  2 |           1 |  1551440567 |
+----+-------------+-------------+----+-------------+-------------+
2 rows in set (0.00 sec)

上述sql就比较好理解了，首先对post_message对自身进行关联，然后查询出自身中，比自身小的数据有几条。那么最大的N条数据，就是从比自身大的数据为空值，到比自身大的数据有N-1条的范围值。

ok，那么完整的sql可以这么理解了，对于每一条数据进行遍历，然后如果处于相应的范围值中，那么这条数据就是符合要求的，取出即可。

另外呢，如果对sql进行优化的话，我们可以按照category_id进行排序，结果如下：

mysql> select a.* from post_message a where 1 >= (select count(*) from post_message where a.category_id = category_id and create_time > a.create_time) order by category_id asc;
+----+-------------+-------------+
| id | category_id | create_time |
+----+-------------+-------------+
|  2 |           1 |  1551440567 |
|  3 |           1 |  1551440568 |
|  4 |           2 |  1551440569 |
|  6 |           2 |  1551440561 |
|  8 |           3 |  1551440563 |
|  9 |           3 |  1551440564 |
+----+-------------+-------------+
6 rows in set (0.00 sec)