场景

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

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)对，将搜索匹配的群组exactly，如果该群组存在，则返回仅包含该群组 ID 的一行，否则返回零行。

即 - 如果实体集匹配entity_ids1和2，它只会返回一个cohort_id哪里的cohort_members正好是 1 和 2，不只是 1，不只是 2，不是一个队列entity_ids1 2 和 3。如果不存在满足此条件的队列，则返回零行。

测试用例

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

http://sqlfiddle.com/#!18/2d022 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(*)
;

这种情况可以通过在中添加复合条件来实现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 http://sqlfiddle.com/#!18/2e67c/2
• 测试用例 #2 http://sqlfiddle.com/#!18/2e67c/3
• 测试用例 #3 http://sqlfiddle.com/#!18/2e67c/4
• 测试用例#4 http://sqlfiddle.com/#!18/2e67c/5
• 测试用例 #5 http://sqlfiddle.com/#!18/2e67c/6
• 测试用例 #6 http://sqlfiddle.com/#!18/2e67c/7

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

UPDATE

如果表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 http://sqlfiddle.com/#!18/2e67c/8
• 测试用例 #2 http://sqlfiddle.com/#!18/7dda5/1
• 测试用例 #3 http://sqlfiddle.com/#!18/361e6/1
• 测试用例#4 http://sqlfiddle.com/#!18/3b6de/1
• 测试用例 #5 http://sqlfiddle.com/#!18/62868/1
• 测试用例 #6 http://sqlfiddle.com/#!18/39008/1