sql - 查找所有子项完全匹配的父项 ID

Question

场景

假设我们有一组代表四个关键概念的数据库表:

1. 实体类型(例如帐户、客户等)
2. 实体(例如上述实体类型的实例)
3. 队列(命名组)
4. 队列成员(构成队列成员的实体)

关于队列的规则是:

1. 一个群组总是至少有一个群组成员。
2. 群组成员对于该群组必须是唯一的(即实体 5 不能两次成为群组 3 的成员，尽管它可以是群组 3 和群组 4 的成员)
3. 没有两个同类群组的成员资格永远相同，尽管一个同类群组可能合法地成为另一个同类群组的子集。

关于实体的规则是:

1. 任何两个实体都不能具有相同的值对 (business_key, entity_type_id)
2. 具有不同 entity_type_id 的两个实体可以共享一个 business_key

因为图片说明了一千行代码，这里是 ERD:

---

问题

我想要一个 SQL 查询，当提供一组 (business_key, entity_type_id) 对时，它将搜索一个完全匹配的队列，返回一个如果该同类群组存在，则仅包含 cohort_id 行，否则为零行。

即- 如果实体集匹配 entity_ids 1 和 2，它只会返回一个 cohort_id，其中 cohort_members 恰好是 1 和 2，而不仅仅是1，不只是 2，不是 entity_ids 1 2 和 3 的群组。如果不存在满足此条件的群组，则返回零行。

---

测试用例

为了帮助人们解决这个问题，我创建了一些表格以及一些定义各种实体类型、实体和群组的数据。还有一个测试数据表，用于匹配，名为test_cohort。它包含 6 个测试队列，用于测试各种场景。前 5 个测试应该只匹配一个队列。第 6 个测试是伪造的，用于测试零行子句。使用测试表时，相关的 INSERT 语句应该只有一行未注释(参见 fiddle，它最初是这样设置的):

http://sqlfiddle.com/#!18/2d022

我在 SQL 中的尝试如下，尽管它未能通过测试 #2 和 #4(可以在 fiddle 中找到):

SELECT actual_cohort_member.cohort_id
FROM test_cohort
INNER JOIN entity
    ON entity.business_key = test_cohort.business_key
    AND entity.entity_type_id = test_cohort.entity_type_id
INNER JOIN cohort_member AS existing_potential_member
    ON existing_potential_member.entity_id = entity.entity_id
INNER JOIN cohort
    ON cohort.cohort_id = existing_potential_member.cohort_id
RIGHT OUTER JOIN cohort_member AS actual_cohort_member
    ON actual_cohort_member.cohort_id = cohort.cohort_id
    AND actual_cohort_member.cohort_id = existing_potential_member.cohort_id
    AND actual_cohort_member.entity_id = existing_potential_member.entity_id
GROUP BY actual_cohort_member.cohort_id
HAVING
    SUM(CASE WHEN
        actual_cohort_member.cohort_id = existing_potential_member.cohort_id AND
        actual_cohort_member.entity_id = existing_potential_member.entity_id THEN 1 ELSE 0
    END) = COUNT(*)
;

Answer 1

这种情况可以通过在 WHERE 子句中添加复合条件来实现，因为您要与对值进行比较。然后，您必须根据 WHERE 子句中设置的条件以及 cohort_id 的总行数来计算结果。

SELECT  c.cohort_id
FROM    cohort c
        INNER JOIN cohort_member cm
            ON c.cohort_id = cm.cohort_id
        INNER JOIN entity e
            ON cm.entity_id = e.entity_id
WHERE   (e.entity_type_id = 1 AND e.business_key = 'acc1')      -- condition here
         OR (e.entity_type_id = 1 AND e.business_key = 'acc2')
GROUP   BY c.cohort_id
HAVING  COUNT(*) = 2                                            -- number must be the same to the total number of condition
        AND (SELECT COUNT(*) 
             FROM cohort_member cm2 
             WHERE cm2.cohort_id = c.cohort_id) = 2             -- number must be the same to the total number of condition

• Test Case #1
• Test Case #2
• Test Case #3
• Test Case #4
• Test Case #5
• Test Case #6

正如您在上面的测试用例中看到的，过滤器中的值取决于 WHERE 子句中的条件数。建议对此创建一个动态查询。

更新

如果 test_cohort 表仅包含一个场景，那么这将满足您的要求，但是，如果 test_cohort 包含场景列表，那么您可能需要查看其他场景回答，因为此解决方案不会改变任何表架构。

SELECT  c.cohort_id
FROM    cohort c
        INNER JOIN cohort_member cm
            ON c.cohort_id = cm.cohort_id
        INNER JOIN entity e
            ON cm.entity_id = e.entity_id
        INNER JOIN test_cohort tc
            ON tc.business_key = e.business_key
                AND tc.entity_type_id = e.entity_type_id
GROUP   BY c.cohort_id
HAVING  COUNT(*) = (SELECT COUNT(*) FROM test_cohort)
        AND (SELECT COUNT(*) 
             FROM cohort_member cm2 
             WHERE cm2.cohort_id = c.cohort_id) = (SELECT COUNT(*) FROM test_cohort)

• Test Case #1
• Test Case #2
• Test Case #3
• Test Case #4
• Test Case #5
• Test Case #6