如何向具有GROUPBY和HAVING子句的查询中再添加一个联接?

一种方法使用 join S是:

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';

若要将另一个透视关系添加到现有透视查询,可以将这些联接作为常规添加,但需要更新 having 子句以仅获取不同的标记ID

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”标签的帖子,帖子的类别是“technology”

也只包括select列表中存在于group by子句中的那些列,因为更新版本的mysql将拒绝这些查询。

CTE应该适用于MySQL5.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";