之前遇到一个朋友问到我的一个问题:如何用一条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)