PostgreSQL查询：找出缺失必填属性的实体Key

问题背景 #

我们有两张数据表：

• attributes_type：存储各实体类型对应的属性定义，其中mandatory字段标记属性是否为必填项
• entities：存储实体的具体属性值记录

需求是找出缺失对应实体类型必填属性的entity_key，预期返回结果为2、4、7。

解决方案查询语句 #

WITH mandatory_attrs AS (
    -- 筛选所有实体类型的必填属性
    SELECT entity_type_key, attributes_type_key
    FROM attributes_type
    WHERE mandatory = true
),
entity_required_pairs AS (
    -- 生成每个实体与其所属类型下所有必填属性的预期配对
    SELECT DISTINCT e.entity_key, ma.attributes_type_key
    FROM entities e
    CROSS JOIN mandatory_attrs ma
    WHERE e.entity_type_key = ma.entity_type_key
)
-- 找出预期配对中未在实体属性记录里出现的实体ID，去重后得到结果
SELECT DISTINCT erp.entity_key
FROM entity_required_pairs erp
LEFT JOIN entities e
    ON erp.entity_key = e.entity_key
    AND erp.attributes_type_key = e.attributes_type_key
WHERE e.entity_key IS NULL
ORDER BY erp.entity_key;

语句逻辑说明 #

1. mandatory_attrs 公共表表达式：先提取所有必填属性及其所属的实体类型，明确各类型下实体必须具备的属性。
2. entity_required_pairs 公共表表达式：通过交叉连接，为每个实体生成它所属类型下所有必填属性的预期组合——也就是这个实体"应该拥有"的属性清单。
3. 最终查询：用左连接对比预期组合和实际存在的属性记录，找不到匹配的条目对应的entity_key，就是缺失必填属性的实体，最后去重并排序输出。

结果验证 #

执行上述语句后，将返回符合预期的结果：2、4、7。

内容的提问来源于stack exchange，提问作者Hari