SQL Server中创建函数以排除DimTestPatient表内DimPatientID的方法

可以创建一个内联表值函数来封装提取DimTestPatient表中所有DimPatientID的逻辑，后续所有需要排除这些测试患者的查询都能直接复用，不用重复编写子查询。

函数创建代码 #

CREATE FUNCTION dbo.GetTestPatientIDs()
RETURNS TABLE
AS
RETURN
(
    -- 返回DimTestPatient表中的所有测试患者ID
    SELECT DimPatientID
    FROM dbo.DimTestPatient
);
GO

替换原有查询逻辑 #

把原查询中的子查询替换为函数调用即可，示例如下：

SELECT 
    COUNT(*)   
FROM 
    dbo.DimPatient dp
LEFT JOIN
    dbo.FactTreatmentHistory th ON dp.DimPatientID = th.DimPatientID
WHERE
    dp.DimPatientID NOT IN (SELECT DimPatientID FROM dbo.GetTestPatientIDs())

更稳妥的写法（规避NULL问题） #

如果DimTestPatient的DimPatientID可能存在NULL值，使用NOT EXISTS比NOT IN更稳妥，不会出现意外的结果：

SELECT 
    COUNT(*)   
FROM 
    dbo.DimPatient dp
LEFT JOIN
    dbo.FactTreatmentHistory th ON dp.DimPatientID = th.DimPatientID
WHERE
    NOT EXISTS (
        SELECT 1 
        FROM dbo.GetTestPatientIDs() tp
        WHERE tp.DimPatientID = dp.DimPatientID
    )

封装的好处 #

• 逻辑统一：后续如果需要调整测试患者的筛选规则（比如添加额外过滤条件），只需要修改这个函数，所有引用的查询都会自动生效
• 性能友好：内联表值函数会被SQL Server优化器当作"参数化视图"处理，执行效率和直接写子查询几乎一致

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