MariaDB -连接-搜索多到多

Question

我在实现多到多个表的“和-串联”搜索时遇到了问题。我试着在下面举一个简单的例子。我用MariaDB。我有一张有流程的桌子。对于流程a可以分配人员和标签。有一张标签表和一张供人使用的桌子。有两个多到多的关系: tags_to_processes和persons_to_processes。

1. 示例:查找person 1和person 2以及标记1和2的所有流程。结果:流程1。
2. 示例:查找person 1和person 2以及标签2的所有流程。结果:过程1和过程2。

非常感谢!

'processes' Table
+-----------+-------------------+
|process_id |process_name       |
+-----------+-------------------+
|1          |Process 1          |
|2          |Process 2          |
|3          |Process 3          |
+-----------+-------------------+

'persons' table
+----------+------------+
|person_id |person_name |
+----------+------------+
|1         |Person 1    |
|2         |Person 2    |
|3         |Person 3    |
|4         |Person 4    |  
|5         |Person 5    |
+----------+------------+

'tags' table
+----------+-----------+
|tag_id    |tag_name   |
+----------+-----------+
|1         |Tag 1      |
|2         |Tag 2      |
|3         |Tag 3      |
|4         |Tag 4      |
|5         |Tag 5      |
|6         |Tag 6      |
+----------+-----------+

'persons_to_processes' table
+----------+-----------+
|person_id |process_id |
+----------+-----------+
|1         |1          |
|2         |1          |
|3         |1          |
|4         |1          |
|5         |1          |
|1         |2          |
|2         |2          |
|4         |3          |
+----------+-----------+

'tags_to_processes' table
+----------+-----------+
|tag_id    |process_id |
+----------+-----------+
|1         |1          |
|2         |1          |
|3         |1          |
|6         |1          |
|2         |2          |
|2         |3          |
+----------+-----------+

Answer 1

您可以将persons_to_processes加入到persons，筛选您想要的人的结果，并使用聚合：

SELECT ptp.process_id
FROM persons_to_processes ptp INNER JOIN persons p
ON p.person_id = ptp.person_id
WHERE p.person_name IN ('Person 1', 'Person 2')
GROUP BY ptp.process_id
HAVING COUNT(*) = 2 -- 2 persons

类似地，对于表tags_to_processes和tags

SELECT ttp.process_id
FROM tags_to_processes ttp INNER JOIN tags t
ON t.tag_id = ttp.tag_id
WHERE t.tag_name IN ('Tag 1', 'Tag 2')
GROUP BY ttp.process_id
HAVING COUNT(*) = 2 -- 2 tags

最后，您可以将这两个查询组合起来，以获得它们与INTERSECT的共同结果。

 WITH 
  cte1 AS (
    SELECT ptp.process_id
    FROM persons_to_processes ptp INNER JOIN persons p
    ON p.person_id = ptp.person_id
    WHERE p.person_name IN ('Person 1', 'Person 2')
    GROUP BY ptp.process_id
    HAVING COUNT(*) = 2 -- 2 persons  
  ),
  cte2 AS (
    SELECT ttp.process_id
    FROM tags_to_processes ttp INNER JOIN tags t
    ON t.tag_id = ttp.tag_id
    WHERE t.tag_name IN ('Tag 1', 'Tag 2')
    GROUP BY ttp.process_id
    HAVING COUNT(*) = 2 -- 2 tags  
  )
SELECT process_id FROM cte1
INTERSECT
SELECT process_id FROM cte2;

见演示。