如何在查询中再添加一个join，该查询有GROUP和which子句？

Question

以下是我的疑问：

SELECT p.*
FROM posts p
INNER JOIN tags_pivot tp 
     ON p.id = tp.post_id
INNER JOIN tags t
     ON t.id = tp.tag_id AND t.name IN ('mysql', 'php')
GROUP BY p.id
HAVING COUNT(*) = 2;

它选择所有带有[mysql]和[php]标记的帖子。我还有一个叫"Category"的存在。这有点像"Tags"，只是每个帖子都是唯一的。

无论如何，我需要将其附加到上面的查询中：

INNER JOIN category_pivote cp
        ON p.id = cp.post_id
INNER JOIN categories c
        ON c.id = cp.category_id AND c.name = "technology"

我如何组合这两个查询？

Answer 1

使用joins的一种方法是：

SELECT p.*
FROM posts p INNER JOIN
     category_pivote cp
     ON p.id = cp.post_id INNER JOIN
     categories c
     ON c.id = cp.category_id AND c.name = 'technology' INNER JOIN
     tags_pivot tp 
     ON p.id = tp.post_id INNER JOIN
     tags t
     ON t.id = tp.tag_id AND t.name IN ('mysql', 'php')
GROUP BY p.id
HAVING COUNT(DISTINCT t.name) = 2;

我添加了DISTINCT，以防止多个类别匹配(尽管这似乎不太可能)。您还可以使用：

HAVING GROUP_CONCAT(DISTINCT t.name ORDER BY t.name) = 'mysql,php';

Answer 2

要将另一个枢轴关系添加到现有的pivot查询中，可以按常规方式添加这些联接，但您需要更新having子句，以便只获取不同的标记ids。

SELECT p.id, p.name
FROM posts p
INNER JOIN tags_pivot tp 
     ON p.id = tp.post_id
INNER JOIN tags t
     ON t.id = tp.tag_id
INNER JOIN category_pivote cp
        ON p.id = cp.post_id
INNER JOIN categories c
        ON c.id = cp.category_id
WHERE t.name IN ('mysql', 'php')
AND c.name = "technology"
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT t.id) = 2;

上面的查询将返回那些具有“mysql”、“php”标签和post是“技术”类别的帖子。

此外，在select列表中只包含那些列，这些列存在于group子句中，因为较新版本的mysql将拒绝这些查询。

Answer 3

CTE应该在MySQL 5.5及更高版本上工作：

with x as (
  SELECT p.*
  FROM posts p
  INNER JOIN tags_pivot tp 
       ON p.id = tp.post_id
  INNER JOIN tags t
       ON t.id = tp.tag_id AND t.name IN ('mysql', 'php')
  GROUP BY p.id
  HAVING COUNT(*) = 2
)
select * from x
  INNER JOIN category_pivote cp
        ON x.id = cp.post_id
  INNER JOIN categories c
        ON c.id = cp.category_id AND c.name = "technology";