试试跟据from_user_id分组获得本组最大自增id,然后再in出来呢。数据量大可能会是个坑,但如果每日几万条数据应该是没问题的,配合created_at索引使用。
SELECT * FROM message WHERE id IN (SELECT MAX(id) message test GROUP BY from_user_id);
SELECT max(created_at),from_user_id,content from messages WHERE created_at like "%2019-08-21%" GROUP BY from_user_id ORDER BY from_user_id desc limit 100;
不知道这个能适应你的需求不;
SELECT
`t`.*
FROM
(
SELECT
`m`.`id`,
`m`.`to_user_id`,
`m`.`from_user_id`,
`u`.`name`,
`m`.`content`,
`m`.`created_at`
FROM
`messages` AS m,
`user` AS u
WHERE
`m`.`to_user_id` = `u`.`id`
ORDER BY
`m`.`created_at` DESC
) AS t
GROUP BY
`t`.`to_user_id`
LIMIT 0,20
关于group by组内排序的问题,mysql官方文档有讲
https://dev.mysql.com/doc/refman/5.6/en/gr... MySQL extends the standard SQL use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.
结论就是除非记录完全相同,否则mysql 不保证每次group by结果相同
推荐文章: