场景
假设我们有一组数据库表,代表四个关键概念:
- 实体类型(例如账户、客户等)
- 实体(上述实体类型的实例)
- 队列(一个命名的组)
- 队列成员(组成队列成员的实体)
队列的规则如下:
- 队列始终至少有一个队列成员。
- 队列成员必须对该队列唯一(即实体5不能是队列3的两个成员,尽管它可以是队列3和队列4的成员)
- 没有两个队列的成员完全相同,尽管一个队列可能合法地是另一个队列的子集。
实体的规则如下:
- 没有两个实体可以具有相同的值对
(business_key, entity_type_id)
- 具有不同
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,最初设置为这样):
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(*)
;