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等資料庫也可以使用子查詢
