代码之家 › 专栏 › 技术社区 › Aistina

使用union时去掉mysql查询中的重复结果

duplicate-removal union mysql

Aistina · 技术社区 · 15 年前

我有一个mysql查询来获取最近有活动的项目。基本上,用户可以发布一个评论或将其添加到他们的心愿单,我想得到所有的项目,要么在过去的X天内有一个新的评论,要么放在某人的心愿单上。

查询有点像这样(稍微简化了一点):

SELECT items.*, reaction.timestamp AS date FROM items
LEFT JOIN reactions ON reactions.item_id = items.id
WHERE reactions.timestamp > 1251806994
GROUP BY items.id

UNION

SELECT items.*, wishlists.timestamp AS date FROM items
LEFT JOIN wishlist ON wishlists.item_id = items.id
WHERE wishlists.timestamp > 1251806994
GROUP BY items.id

ORDER BY date DESC LIMIT 5

这是可行的,但当一个项目同时被放在某人的心愿单上时和已发布评论,该项目将返回两次。 UNION 通常删除重复项,但由于 date 两行不同,返回两行。我能告诉MySQL删除重复行时忽略日期吗?

我也试过这样做:

SELECT items.*, IF(wishlists.id IS NOT NULL, wishlists.timestamp, reactions.timestamp) AS date FROM items
LEFT JOIN reactions ON reactions.item_id = items.id
LEFT JOIN wishlist ON wishlists.item_id = items.id

WHERE (wishlists.id IS NOT NULL AND wishlists.timestamp > 1251806994) OR
(reactions.id IS NOT NULL AND reactions.timestamp > 1251806994)
GROUP BY items.id

ORDER BY date DESC LIMIT 5

但由于某种原因,这个过程非常缓慢(大约花了半分钟)。

3 回复 | 直到 15 年前

Aistina 15 年前

SELECT * FROM (
    SELECT items.*, reaction.timestamp AS date FROM items
    LEFT JOIN reactions ON reactions.item_id = items.id
    WHERE reactions.timestamp > 1251806994
    GROUP BY items.id

    UNION

    SELECT items.*, wishlists.timestamp AS date FROM items
    LEFT JOIN wishlist ON wishlists.item_id = items.id
    WHERE wishlists.timestamp > 1251806994
    GROUP BY items.id

    ORDER BY date DESC LIMIT 5
) AS items

GROUP BY items.id
ORDER BY date DESC LIMIT 5

Lawrence Barsanti 15 年前

SELECT item_field_1, item_field_2, ..., max(date) as date
FROM
  (the query you posted) 
GROUP BY item_field_1, item_field_2, ...

David M 15 年前


SELECT item.*, GREATEST(COALESCE(wishlists.timestamp, 0), COALESCE(reaction.timestamp, 0)) as date
FROM items
LEFT JOIN reactions ON reactions.item_id = items.id AND reactions.timestamp > 1251806994
LEFT JOIN wishlists ON wishlists.item_id = items.id AND wishlists.timestamp > 1251806994
ORDER BY date DESC limit 5