mysql 组内排序_MySQL GROUP BY 组内排序

需求：显示最新的3条帖子(要求同一用户只取最新一条)

方法一：先排好序，再group by 达到同一用户只取最新一条(注意，MYSQL5.7版本的子查询的排序需要加LIMIT 排序才有效：引用)

SELECT

*

FROM

(

SELECT

t1.*,

t2.nick_name,

t2.head_portrait

FROM

tb_circle_post t1,

tb_user t2

WHERE

t1.user_id = t2.id AND t1.is_delete = 1

ORDER BY

t1.create_time DESC, t1.likes DESC

LIMIT 0, 50

) temp

GROUP BY

user_id

ORDER BY

create_time DESC, likes DESC

LIMIT 0, 3

方法二：使用DISTINCT的这种写法莫名其妙地  子查询的排序生效

SELECT

temp.*

FROM

(

SELECT DISTINCT

( t1.user_id ),

t1.order_amount,

t1.create_time,

t2.nick_name,

t2.head_portrait

FROM

tb_order_goods t1,

tb_user t2

WHERE

t1.user_id = t2.id

AND t1.is_delete = 1

ORDER BY

t1.create_time DESC

) temp

GROUP BY

temp.user_id

ORDER BY

temp.create_time DESC

LIMIT 0, 3

方法三：前两种方法都有不确定性：就是子查询的排序是否在最后一层查询中生效(具体可能需要explain查看执行计划)

最后的方法我也是觉得最合理的。

先子查询查出每个用户发帖子的最新时间，再用这个时间和用户ID去查多一次帖子表。

SELECT

t.*

FROM

(

SELECT

user_id,

max(createTime) AS max_createTime

FROM

t

GROUP BY

user_id

) t2

LEFT JOIN t ON t.user_id = t2.user_id

AND t.createTime = t2.max_createTime

ORDER BY

t2.max_createTime DESC

LIMIT 3