mysql - 组合两个 SQL 语句，其中在多对多关系中两者都必须为 true

Question

我对 SQL 还很缺乏经验，我正在尝试找出如何结合查询的两个要求。

首先是模型，以简化对需求的理解:

我需要一组结果，获取与一个列表中的任一值匹配的行，我将此结果称为 1:

SELECT * FROM
resource r
inner join 
category_resource cr
on (r.id = cr.resource_id)
where cr.category_id in (8,9)

但是还有第二个要求，我将其称为结果 2:类似的语句，但也必须与其他值匹配(通过相同的行)才能匹配这些行中的任何一个:

SELECT * FROM
resource r
inner join 
category_resource cr
on (r.id = cr.resource_id)
where cr.category_id in (10,11,12)

当然，我觉得这在某种程度上是多余的，应该有一种更简单的方法将其全部写在一个语句中。但重点是，这不满足要求:

SELECT * FROM
resource r
inner join 
category_resource cr
on (r.id = cr.resource_id)
where cr.category_id in (8,9,10,11,12)

因为这相当于说匹配结果 1 或结果 2。但我需要的是匹配结果 1 和结果 2。即，类似:

SELECT * FROM
resource r
inner join 
category_resource cr
on (r.id = cr.resource_id)
where cr.category_id in (8,9) AND (10,11,12)

但这不起作用(可能根本不正确)...

最后用简单的英语来说，我想要的是找到具有类别 8 OR 9 的任何资源，并且具有类别 10 OR 11 OR 12。

那么我怎样才能尽可能简单地完成这样的事情呢？

编辑:

我忘记了一项要求:

我还必须获取每个结果行所属的类别(无论查询中是否要求这些类别。即，即使我只要求属于 (8 OR 9) AND (10 OR 11 OR 12)，如果资源匹配，我想知道它所属的所有类别，即使它们还可能包括 13 和 14...

我有一个类似的要求，在这之前我已经解决了:

SELECT
  r.id, r.title,
  u.name AS 'created_by',
  GROUP_CONCAT( CONCAT(CAST(c.id as CHAR),',',c.name,',',c.value) separator ';') AS 'Categories'
FROM
  resource r
  INNER JOIN 
/*Select matching records as a table*/
(SELECT
    resource_id
  FROM
    category_resource
  WHERE
    category_id IN (9,10,11,12,13,14,15)) mr
    ON r.id = mr.resource_id
  INNER JOIN category_resource cr
    ON r.id = cr.resource_id
  INNER JOIN category c
    ON cr.category_id = c.id
  INNER JOIN user u
    ON r.created_by = u.id
GROUP BY r.id;

但是该声明当然不包含这一最新要求。我可以将其合并并获得包括每个结果所属的所有类别的结果吗？

mrjink 的回答似乎非常有效。我还使用相同的模式进行了测试，以沿着相同的路线添加更多标准，并且它似乎效果很好:

SELECT
  r.id, r.title,
  u.name AS 'created_by',
  GROUP_CONCAT( CONCAT(CAST(c.id as CHAR),',',c.name,',',c.value) separator ';') AS 'Categories'
FROM
  resource r
  INNER JOIN 
/*Select matching records as a table*/
  (SELECT
   DISTINCT r.id AS id
  FROM
   resource r
  INNER JOIN
   category_resource cr1 ON (r.id = cr1.resource_id)
  INNER JOIN
   category_resource cr2 ON (r.id = cr2.resource_id)
/*For more criteria, I can just add an inner join here with another alias...*/
INNER JOIN
   category_resource cr3 ON (r.id = cr3.resource_id)
INNER JOIN
   category_resource cr4 ON (r.id = cr4.resource_id)
  WHERE
   cr1.category_id IN (8, 9)
  AND
   cr2.category_id IN (10)
/*and add the corresponding ADD clause here for the same alias...*/
  AND
   cr3.category_id IN (12)
  AND
   cr4.category_id IN (14)) mr

    ON r.id = mr.id
  INNER JOIN category_resource cr
    ON r.id = cr.resource_id
  INNER JOIN category c
    ON cr.category_id = c.id
  INNER JOIN user u
    ON r.created_by = u.id
GROUP BY r.id;

这样，如果用户选择它们(正如意图)，我可以以编程方式添加更多条件。从我所看到的来看，虽然如果手动编写或读取它们，这可能会创建非常复杂的 SQL 语句，但它似乎运行良好，并且不会以负面方式影响性能(如果有的话，添加更多条件似乎会加快速度)上升，大概是因为点击率越来越低？)

Answer 1

加入两次!

这样的事情应该有效:

SELECT
  r.id, r.title,
  u.name AS 'created_by',
  GROUP_CONCAT( CONCAT(CAST(c.id as CHAR),',',c.name,',',c.value) separator ';') AS 'Categories'
FROM
  resource r
  INNER JOIN 
/*Select matching records as a table*/

  (SELECT
   DISTINCT r.id AS id
  FROM
   resource r
  INNER JOIN
   category_resource cr1 ON (r.id = cr1.resource_id)
  INNER JOIN
   category_resource cr2 ON (r.id = cr2.resource_id)
  WHERE
   cr1.category_id IN (8, 9)
  AND
   cr2.category_id IN (10, 11, 12)) mr

    ON r.id = mr.id
  INNER JOIN category_resource cr
    ON r.id = cr.resource_id
  INNER JOIN category c
    ON cr.category_id = c.id
  INNER JOIN user u
    ON r.created_by = u.id
GROUP BY r.id;