MySQL -搜索与联合所有和组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

I am trying to group the post ID's so I do not return duplicate search results, but for some reason there are duplicates even when I use the GROUP BY p.id. Can someone tell me what I am doing wrong?

我试图对post ID进行分组，这样我就不会返回重复的搜索结果，但是出于某种原因，即使我使用p.id组，也会有重复的结果。有人能告诉我我做错了什么吗?

2 个解决方案

#1

The GROUP BY will group results of your third part of query only. It will first GROUP, then UNION.

组将只对第三部分查询的结果进行分组。它首先是群体，然后是联合。

Enclose the whole query into a subquery and GROUP on it.

将整个查询封装到子查询中并对其进行分组。

#2

Here is an example that doesn't work with MySQL :

这里有一个不适用于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

#1