PHP笔记网

需求：显示最新的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

参考：MySQL 用 GROUP BY 分组查询，结果只取对应最先（或最近）一条记录