MySQL数据去重的问题

Question

SELECT
	b.id,
	b.user_id,
	b.content,
	b.pick_remain,
	u.username,
	u.avatar,
	u.sex,
	u.accid AS uniqueCode,
	u.vip_type,
	u.vip_start,
	u.vip_end 
FROM
	tbl_bottle b
	LEFT JOIN ( SELECT bottle_id FROM tbl_pick_bottle WHERE user_id = 9344 ) AS tpb ON b.id <> tpb.bottle_id
	LEFT JOIN tbl_view tv ON b.id <> tv.bottle_id
	LEFT JOIN tbl_users AS u ON b.user_id = u.id 
WHERE
	 b.STATUS = 1 
	AND b.pick_remain >= 1 
and b.id >= (select max(id) from tbl_bottle) - 6
	LIMIT 7

这个sql目前0.7秒可以查询出来数据， 目前想加一个 b.user_id的数据去重。 

使用groupby的话 时间会在20秒以上了， 使用distinct的话不知道怎么做单字段的

Answer 1

用窗口函数ROW_NUMBER()先对表tbl_bottle 里的user_id去重。我用id字段排序的，你看用什么字段排序合适。

SELECT
	b.id,
	b.user_id,
	b.content,
	b.pick_remain,
	u.username,
	u.avatar,
	u.sex,
	u.accid AS uniqueCode,
	u.vip_type,
	u.vip_start,
	u.vip_end 
FROM
    (   SELECT *,
               ROW_NUMBER() OVER(PARTITION user_id ORDER BY id DESC) AS RN
	    FROM tbl_bottle 
    ) AS b
	LEFT JOIN ( SELECT bottle_id FROM tbl_pick_bottle WHERE user_id = 9344 ) AS tpb ON b.id <> tpb.bottle_id
	LEFT JOIN tbl_view tv ON b.id <> tv.bottle_id
	LEFT JOIN tbl_users AS u ON b.user_id = u.id 
WHERE
    b.RM = 1
	AND  b.STATUS = 1 
	AND b.pick_remain >= 1 
and b.id >= (select max(id) from tbl_bottle) - 6
	LIMIT 7