sql中实现先排序后分组
- 2019 年 11 月 7 日
- 筆記
数据表结构和数据如下：
CREATE TABLE `commun_message_chat_single` ( `id` int(11) NOT NULL AUTO_INCREMENT, `chat_id` int(11) DEFAULT '0' COMMENT '会话id', `from_id` varchar(11) DEFAULT NULL COMMENT '发送者 用户id', `to_id` varchar(11) DEFAULT NULL COMMENT '接收者 用户id', `content` text COMMENT '消息内容', `type` tinyint(1) DEFAULT '1' COMMENT '消息类型 1：文字 2：图片 3：文件', `send_time` datetime DEFAULT NULL COMMENT '消息发送时间', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='单聊 聊天记录'; -- 插入数据的sql INSERT INTO `commun_message_chat_single` VALUES (60, 10, '11', 'md_1', '123', 1, '2019-10-16 08:25:50'); INSERT INTO `commun_message_chat_single` VALUES (61, 10, '11', 'md_1', '456', 1, '2019-10-28 08:25:59'); INSERT INTO `commun_message_chat_single` VALUES (62, 10, '11', 'md_2', '789', 1, '2019-10-01 08:26:21'); INSERT INTO `commun_message_chat_single` VALUES (63, 10, '11', 'md_2', '哈哈哈', 1, '2019-10-27 08:26:34'); INSERT INTO `commun_message_chat_single` VALUES (64, 10, '11', 'md_2', '测试测试', 1, '2019-10-10 08:28:27');
目前数据表所有数据如下：
mysql> select * from commun_message_chat_single where from_id = '11'; +----+---------+---------+-------+--------------+------+---------------------+ | id | chat_id | from_id | to_id | content | type | send_time | +----+---------+---------+-------+--------------+------+---------------------+ | 60 | 10 | 11 | md_1 | 123 | 1 | 2019-10-16 08:25:50 | | 61 | 10 | 11 | md_1 | 456 | 1 | 2019-10-28 08:25:59 | | 62 | 10 | 11 | md_2 | 789 | 1 | 2019-10-01 08:26:21 | | 63 | 10 | 11 | md_2 | 哈哈哈 | 1 | 2019-10-27 08:26:34 | | 64 | 10 | 11 | md_2 | 测试测试 | 1 | 2019-10-10 08:28:27 | +----+---------+---------+-------+--------------+------+---------------------+ 5 rows in set (0.00 sec)
需求：查询from_id为11的数据 并且 和 每一个to_id 按照时间排序显示最新的一条数据（也就是显示：to_id是md_1的，按照时间排序id为61的符合结果；to_id是md_2的，按照时间排序id为63的符合结果）
符合该需求的2条数据如下：
+----+---------+---------+-------+-----------+------+---------------------+ | id | chat_id | from_id | to_id | content | type | send_time | +----+---------+---------+-------+-----------+------+---------------------+ | 61 | 10 | 11 | md_1 | 456 | 1 | 2019-10-28 08:25:59 | | 63 | 10 | 11 | md_2 | 哈哈哈 | 1 | 2019-10-27 08:26:34 | +----+---------+---------+-------+-----------+------+---------------------+
实现该需求的sql语句如下（利用sql中的子查询）：
SELECT * FROM (SELECT * FROM commun_message_chat_single WHERE from_id = '11' ORDER BY send_time DESC ) as temp_table GROUP BY temp_table.to_id; -- 大概解释下该条sql语句：括号内的子查询是查询from_id为11的数据并且按照send_time从高到低排序,这里的子查询的结果会生成一个临时表,临时表这里取名为temp_table,然后外部查询将temp_table的结果进行分组。
mysql5.7版本以下执行结果如下（只在5.5和5.6版本试过）：
+----+---------+---------+-------+-----------+------+---------------------+ | id | chat_id | from_id | to_id | content | type | send_time | +----+---------+---------+-------+-----------+------+---------------------+ | 61 | 10 | 11 | md_1 | 456 | 1 | 2019-10-28 08:25:59 | | 63 | 10 | 11 | md_2 | 哈哈哈 | 1 | 2019-10-27 08:26:34 | +----+---------+---------+-------+-----------+------+---------------------+ 2 rows in set (0.00 sec)
mysql5.7版本执行结果如下：
+----+---------+---------+-------+---------+------+---------------------+ | id | chat_id | from_id | to_id | content | type | send_time | +----+---------+---------+-------+---------+------+---------------------+ | 60 | 10 | 11 | md_1 | 123 | 1 | 2019-10-16 08:25:50 | | 62 | 10 | 11 | md_2 | 789 | 1 | 2019-10-01 08:26:21 | +----+---------+---------+-------+---------+------+---------------------+ 2 rows in set (0.00 sec)
what？为啥5.7以下的版本是我们想要的结果，而5.7版本的执行结果居然不是我们期待的结果！高版本居然执行的结果不正确。。
为什么mysql5.7和5.7以下的版本会有不同的结果呢？
可以分别查看一下这条sql语句在两个不同版本数据库的sql执行计划：
mysql 5.7.21：
mysql5.5.62和5.6.44：
对比可以发现5.7版本的mysql在执行这条sql语句的时候缺少了一个derived的操作，通过查阅相关资料了解到mysql5.7对子查询进行了优化，认为子查询中的order by可以进行忽略，只要Derived table里不包含如下条件就可以进行优化：
①、UNION clause
②、GROUP BY
③、DISTINCT
④、Aggregation
⑤、LIMIT or OFFSET
看到了吧，如果要在mysql5.7中实现先排序后分组，这里可以加个limit，不过你的limit要足够大
mysql5.7解决办法如下：
SELECT * FROM (SELECT * FROM commun_message_chat_single WHERE from_id = '11' ORDER BY send_time DESC LIMIT 10000 ) as temp_table GROUP BY temp_table.to_id;
mysql8.0及以上版本没试过，不过应该和5.7是一样的效果，都进行了优化。
PS：子查询不是mysql独有的，sqlserver等数据库也可以使用子查询
