MySQL的 - 与UNION ALL和GROUP BY搜索

问题描述：

SELECT p.id, p.title, p.uri, 'post' AS search_type 
FROM `posts` AS p 
WHERE title LIKE "%logo%" 

UNION ALL 

SELECT p.id, p.title, p.uri, 'tag' AS search_type 
FROM posts AS p 
INNER JOIN post_tags AS pt ON pt.post_id = p.id 
INNER JOIN tags AS t ON pt.tag_id = t.id 
WHERE t.title LIKE "%logo%" 

UNION ALL 

SELECT p.id, p.title, p.uri, 'category' AS search_type 
FROM posts AS p 
INNER JOIN post_categories AS pc ON pc.post_id = p.id 
INNER JOIN categories AS c ON pc.category_id = c.id 
WHERE c.title LIKE "%logo%" 

GROUP BY p.id 
LIMIT 30

我想组帖子的ID，所以我不返回重复的搜索结果，但由于某些原因，有重复，甚至当我使用GROUP BY p.id。有人能告诉我我做错了什么吗？MySQL的 - 与UNION ALL和GROUP BY搜索

你只需要每p.id一个结果，无论搜索类型的？ – 2010-10-11 10:25:40

嘿马克，不，我想要所有的结果。 search_type只允许我定义搜索结果来自哪里（发布，标签或类别）。 – Torez 2010-10-13 21:59:35

答

GROUP BY将仅对您的第三部分查询的结果进行分组。它将首先是GROUP，然后是UNION。

将整个查询放入子查询和GROUP中。

答

这里是不使用MySQL的一个例子：

SELECT Column1, SUM(Column2) AS Column2Total 
FROM 
(
    SELECT Column1, Column2 FROM Table1 
    UNION ALL 
    SELECT Column1, Column2 FROM Table2 
) AS UnionTable 
GROUP BY Column1

MySQL的 - 与UNION ALL和GROUP BY搜索

相关推荐