sql中实现先排序后分组

数据表结构和数据如下:

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:

m1.png


mysql5.5.62和5.6.44:

m2.png


对比可以发现5.7版本的mysql在执行这条sql语句的时候缺少了一个derived的操作,通过查阅相关资料了解到mysql5.7对子查询进行了优化,认为子查询中的order by可以进行忽略,只要Derived table里不包含如下条件就可以进行优化:

①、UNION clause

②、GROUP BY

③、DISTINCT

④、Aggregation

⑤、LIMIT or OFFSET


详情可查看:5.7中Derived table变形记


看到了吧,如果要在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等数据库也可以使用子查询



靠样子,半辈子;靠才华,一辈子。



声明:禁止任何非法用途使用,凡因违规使用而引起的任何法律纠纷,本站概不负责。

扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

精彩评论

全部回复12人评论7,777人参与