试试跟据 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 结果相同
我这样做，效果已达到。
DB::table('messages as t1')
->leftJoin('messages as t2', function ($join) {
$join->on('t1.from_user_id', '=', 't2.from_user_id')->on('t1.id', '<', 't2.id');
})
->leftJoin('user as t3', 't1.from_user_id', '=', 't3.id')
->where('t1.to_user_id', $id)
->whereNull('t2.id')->paginate(15);
关于 LearnKu
粤公网安备 44030502004330号
推荐文章：