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

参考：

1. https://bbs.csdn.net/topics/391946106
2. https://jingyan.baidu.com/article/4ae03de3df93cc3eff9e6b37.html
3. https://www.v2ex.com/t/314489
4. https://blog.csdn.net/fdipzone/article/details/72453553