查找所有子节点完全匹配的父节点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) 对时，将搜索与之完全匹配的队列，如果该队列存在，则返回一个只包含队列ID的行，否则返回零行。

即- 如果实体集合与entity_ids 1和2匹配，它只会返回一个cohort_id，其中cohort_members正好是1和2，而不仅仅是1，也不仅仅是2，也不是具有entity_ids 1、2和3的队列。如果不存在满足此条件的队列，则返回零行。

---

测试用例

为了帮助回答问题的人，我创建了一个包含各种实体类型、实体和队列定义数据的表fiddle。还有一个名为test_cohort的包含匹配测试数据的表。它包含6个测试队列，测试各种情况。前5个测试应该恰好匹配一个队列。第6个测试是一个伪造的测试，用于测试零行子句。在使用测试表时，相关的INSERT语句应该只取消注释一行（请参见fiddle，最初设置为这样）：

我的 SQL 尝试如下，但它未通过测试 #2 和 #4（可以在 fiddle 中找到）：

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

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(*)
;

- e_i_pi

---

请注意，我打算在周一至少用100个积分奖励这个问题。 - e_i_pi

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

• 测试用例＃1
• 测试用例＃2
• 测试用例＃3
• 测试用例＃4
• 测试用例＃5
• 测试用例＃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)

• 测试用例#1
• 测试用例#2
• 测试用例#3
• 测试用例#4
• 测试用例#5
• 测试用例#6

这是一个包含6个测试用例的列表，每个测试用例都有一个链接。

- John Woo

---

我需要查询通用地接受一个表格（因此是 test_cohort），并对其进行测试。在查询中硬编码值并不是理想的选择。 - e_i_pi

@e_i_pi表中的test_cohert只会包含每个场景的测试数据吗？如果是这样的话，请尝试查看这个修改后的查询：http://sqlfiddle.com/#!18/2e67c/8 - John Woo

John，我明天会看一下，今晚要和孩子们一起看电影。 - e_i_pi

John，请你更新你的答案，加上你提供的新fiddle链接和其中的SQL语句，这样未来寻找类似内容的人就有一个可靠的参考点了。 - e_i_pi

等待18小时才能颁发奖励。我认为此答案是最优秀的。 - e_i_pi

Answer 2

我已经在你的 test_cohort 表中添加了一列 i，这样你就可以同时测试所有场景。以下是 DDL：

CREATE TABLE test_cohort (
i int,
business_key NVARCHAR(255),
entity_type_id INT
);

INSERT INTO test_cohort VALUES
(1, 'acc1', 1), (1, 'acc2', 1) -- TEST #1: should match against cohort 1
,(2, 'cli1', 2), (2, 'cli2', 2) -- TEST #2: should match against cohort 2
,(3, 'cli1', 2) -- TEST #3: should match against cohort 3
,(4, 'acc1', 1), (4, 'acc2', 1), (4, 'cli1', 2), (4, 'cli2', 2) -- TEST #4: should match against cohort 4
,(5, 'acc1', 1), (5, 'cli2', 2) -- TEST #5: should match against cohort 5
,(6, 'acc1', 3), (6, 'cli2', 3) -- TEST #6: should not match any cohort

并且查询：

select
    c.i, m.cohort_id
from
    (
        select 
            *, cnt = count(*) over (partition by i)
        from 
            test_cohort
    ) c
    join entity e on c.entity_type_id = e.entity_type_id and c.business_key = e.business_key
    join (
        select
            *, cnt = count(*) over (partition by cohort_id)
        from
            cohort_member
    ) m on e.entity_id = m.entity_id and c.cnt = m.cnt
group by m.cohort_id, c.cnt, c.i
having count(*) = c.cnt

输出

i   cohort_id
------------
1   1
2   2
3   3
4   4
5   5

这个想法是在连接之前计算行数并通过精确匹配进行比较。

- uzi

---

这可能是我需要的。今晚我在陪家人，明天我可以试试这个。 - e_i_pi

有时候我希望我可以多次奖励正确答案。这个答案返回了正确的结果，但是我更喜欢 John Woo 的答案，因为它在 HAVING 子句中包含了聚合逻辑，我认为这是更简洁的解决方案。 - e_i_pi

没问题。这取决于你和你的偏好。 - uzi