苏范霖 2021-05-21 11:51 采纳率: 0%
浏览 43
已结题

MySQL数据去重的问题

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的话不知道怎么做单字段的

  • 写回答

1条回答 默认 最新

  • benbenli 2021-05-21 12:03
    关注

    用窗口函数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
    
    评论

报告相同问题?