问题的答案

问题的历史

关系除法由埃德加·F·科德于1970年正式定义为笛卡尔积的逆操作，旨在在关系代数中表达普遍量化（∀）。虽然ANSI SQL自然通过WHERE子句和连接实现存在量化（∃），但它缺乏原生的除法运算符，迫使开发人员使用逻辑否定或计数策略来模拟这一集合论操作。此模式在监管合规、授权矩阵和能力跟踪系统中持续出现，在这些情况下，识别“完整集合”至关重要。

问题

假设有一个被除数表EmployeeTraining(employee_id, module_id)和一个除数表RequiredModules(module_id)，目的是返回与除数中的所有行相关联的每个employee_id。这个挑战超出了简单的连接，它找到的是任何匹配；除法需求验证整体覆盖率。关键是，解决方案必须处理重复的完成记录、空的要求集（虚无真理），并在没有过程逻辑的情况下高效执行。

解决方案

标准的ANSI SQL方法采用双重否定：选择那些不存在尚未完成的必需模块的员工。这转化为嵌套的NOT EXISTS子句。或者，计数方法对比独特的完成情况与所需总数，尽管需要精心处理重复。

-- 双重否定：纯关系除法
SELECT DISTINCT e.employee_id
FROM EmployeeTraining e
WHERE NOT EXISTS (
    SELECT 1
    FROM RequiredModules r
    WHERE NOT EXISTS (
        SELECT 1
        FROM EmployeeTraining e2
        WHERE e2.employee_id = e.employee_id
          AND e2.module_id = r.module_id
    )
);

-- 计数方法（处理重复）
SELECT employee_id
FROM (
    SELECT e.employee_id, 
           COUNT(DISTINCT e.module_id) AS completed_count
    FROM EmployeeTraining e
    JOIN RequiredModules r ON e.module_id = r.module_id
    GROUP BY e.employee_id
) sub
WHERE completed_count = (SELECT COUNT(*) FROM RequiredModules);

生活中的情况

一家航空维修公司需要认证机械师进行发动机维修。FAA要求完成五个特定的安全模块，这些模块在Mechanic_Completions中进行跟踪，但机械师经常重修未通过的模块，导致重复记录。对于1200名机械师和200个可能的模块，每天运行此检查需要一条忽略重复并处理审核场景（当要求列表可能暂时为空）的查询。

解决方案1：使用COUNT(DISTINCT)的GROUP BY 这种方法连接了表，按机械师进行分组并比较独特的计数。主要优势在于可读性；初级开发人员能立即理解逻辑。然而，由于DISTINCT操作在200万条历史记录上存在显著的性能下降。更为严重的是，在没有显式的COALESCE处理的情况下，当RequiredModules表为空（审核模式）时，它返回了零个机械师，违反了数学原理，即对空集的普遍量化对所有元素是虚无真理。

解决方案2：使用NOT EXISTS的双重否定 这种方法使用两个嵌套的NOT EXISTS子句检查缺失的模块。它自然处理重复的完成记录，因为它只检查存在性（半连接行为），而不是计数发生次数。它在要求集为空时正确返回所有机械师。缺点是涉及更复杂的执行计划；优化器有时选择嵌套循环连接而不是哈希连接，尽管在module_id上适当的索引减轻了这个问题。

选择的解决方案和结果 团队选择了双重否定的方法，因为数据完整性规则允许重复的完成条目，使得计数方法在没有昂贵的DISTINCT操作的情况下显得冒险。该查询在150毫秒内识别出847名完全认证的机械师，而在随后的监管审计中，所有要求暂时暂停时，该查询正确识别出所有1200名机械师为合规（虚无真理），防止了不必要的停工，同时保持了逻辑正确性。

候选人常常忽视的内容

当RequiredModules表包含零行时，查询的行为如何，这在数学上为何重要？

当除数为空时，关系除法必须返回整个被除数集（所有员工），因为虚无真理规定每个元素满足“对于空集中的所有项目”。双重否定方法自然实现了这一点；因为没有必需的模块，内部的NOT EXISTS从未找到缺失的模块，因此外部子句不会排除任何人。相反，计数方法completed_count = (SELECT COUNT(*) FROM RequiredModules)将计数等同于零，仅返回零个完成的机械师。候选人必须实现COALESCE包装或CASE逻辑，当除数为空时返回所有行，或者使用处理此边缘情况的双重否定模式。

使用COUNT(*)而不是COUNT(DISTINCT module_id)的计数方法为何会产生误报，重复如何影响双重否定的方法？

如果一个机械师完成了模块A两次（最初未通过，然后重修），COUNT(*)返回2。如果仅要求模块A和B，一个缺少B但有两个A记录的机械师显示出计数为2，错误地满足等式检查。这造成了关键的合规性漏洞。候选人经常忽视DISTINCT，假设外键约束阻止重复项。双重否定方法仅检查存在性（SELECT 1），使其对被除数表中的重复行免疫；只要存在任何关联，模块就得到了满足。理解这种区别对于没有完美唯一性约束的数据环境至关重要。

精确关系除法和有余数的除法之间有什么区别，如果要修改查询以查找那些仅完成所需模块而没有额外的员工，应该怎样做？

以上解决方案实施了“有余数的除法”（松散除法），返回具有至少所需模块的员工（超集）。精确除法要求员工没有超过那些必需的额外模块。为了实现这一点，候选人必须添加筛选条件，确保机械师的总独特模块计数等于所需计数：HAVING COUNT(DISTINCT module_id) = (SELECT COUNT(*) FROM RequiredModules)。许多候选人错误地假设关系除法意味着“仅此而已”，导致授权漏洞，员工错误地批准执行敏感任务的过期或不适当额外认证。

在验证强制性检查清单的完全合规性时，当部分满足不够时，如何在ANSI SQL中实施关系除法以识别满足每项要求的实体，而不依赖于GROUP BY聚合?

问题的历史

问题

解决方案

生活中的情况

候选人常常忽视的内容