SQL Server中按规则筛选覆盖行：自定义函数问题修复

我有一张表，主键为Id，还包含Identifier和ProfileID列。需根据指定Id按以下规则筛选数据：

• 规则1：若该Id对应行无关联Profile，直接返回该行；
• 规则2：若该Id行有Profile，则查找同Identifier且无Profile的行（若存在）；
• 规则3：若以上都不满足，返回该Id带Profile的行。

测试表 #

预期结果 #

• 查询Id=1：返回行1（规则1）；
• 查询Id=2：返回行3（规则2）；
• 查询Id=3：返回行3（规则1）；
• 查询Id=4：返回行4（规则3）。

自定义函数代码 #

CREATE FUNCTION GetModelsToUse2(@modelId INT)
RETURNS TABLE
AS RETURN 
WITH UserDefined AS (
    SELECT Id, Identifier
    FROM Models
    WHERE Id = @modelId AND ProfileId IS NULL
), Profiled AS (
    SELECT Id, Identifier
    FROM Models
    WHERE Id = @modelId AND ProfileId IS NOT NULL
)
SELECT DISTINCT dm.*
FROM Models dm
WHERE dm.Id in (SELECT Id FROM UserDefined) OR
      NOT(dm.Id IN (SELECT Id FROM UserDefined)) AND (
            dm.identifier in (SELECT identifier FROM Profiled WHERE id = @modelId) and dm.profileId IS NULL OR
            NOT(dm.identifier in (SELECT identifier FROM Profiled WHERE id = @modelId) and dm.profileId IS NULL) AND dm.Id = @modelId)

该函数对Id=1、3、4的查询结果正确，但查询Id=2时同时返回行2和行3，请问如何修复？

问题根源 #

原函数的WHERE条件存在逻辑优先级错误：OR的优先级低于AND，导致规则2和规则3的条件没有形成互斥关系。当查询Id=2时，既匹配规则2的行3，又错误触发了规则3的行2，最终返回两行。

修复后的函数（方案一：优先级排序法） #

通过给符合规则的行标记优先级，确保只返回优先级最高的行：

CREATE FUNCTION GetModelsToUse2(@modelId INT)
RETURNS TABLE
AS RETURN 
WITH TargetModel AS (
    SELECT 
        Id, 
        Identifier, 
        ProfileId
    FROM Models
    WHERE Id = @modelId
),
CandidateRows AS (
    SELECT 
        m.*,
        CASE
            -- 规则1：目标行本身无Profile，优先级最高
            WHEN m.Id = @modelId AND m.ProfileId IS NULL THEN 1
            -- 规则2：同Identifier且无Profile的行（仅当目标行有Profile时）
            WHEN (SELECT ProfileId FROM TargetModel) IS NOT NULL 
                 AND m.Identifier = (SELECT Identifier FROM TargetModel) 
                 AND m.ProfileId IS NULL THEN 2
            -- 规则3：目标行本身（当规则2无匹配时）
            WHEN m.Id = @modelId THEN 3
            ELSE 4
        END AS Priority
    FROM Models m
)
SELECT TOP 1 *
FROM CandidateRows
WHERE Priority IN (1,2,3)
ORDER BY Priority ASC;

修复后的函数（方案二：互斥条件分支法） #

将三个规则拆分为互斥的条件分支，确保同一时间仅触发一条规则：

CREATE FUNCTION GetModelsToUse2(@modelId INT)
RETURNS TABLE
AS RETURN 
WITH TargetModel AS (
    SELECT Id, Identifier, ProfileId
    FROM Models
    WHERE Id = @modelId
)
SELECT m.*
FROM Models m
WHERE 
    -- 规则1：目标行无Profile，直接返回
    (SELECT ProfileId FROM TargetModel) IS NULL AND m.Id = @modelId
    -- 规则2：目标行有Profile，且存在同Identifier无Profile的行，返回这些行
    OR (
        (SELECT ProfileId FROM TargetModel) IS NOT NULL
        AND EXISTS (SELECT 1 FROM Models WHERE Identifier = (SELECT Identifier FROM TargetModel) AND ProfileId IS NULL)
        AND m.Identifier = (SELECT Identifier FROM TargetModel) AND m.ProfileId IS NULL
    )
    -- 规则3：目标行有Profile，且无同Identifier无Profile的行，返回目标行
    OR (
        (SELECT ProfileId FROM TargetModel) IS NOT NULL
        AND NOT EXISTS (SELECT 1 FROM Models WHERE Identifier = (SELECT Identifier FROM TargetModel) AND ProfileId IS NULL)
        AND m.Id = @modelId
    );

修复说明 #

1. 先通过CTE获取目标行的基础信息，避免重复子查询计算；
2. 明确规则的优先级顺序，用互斥条件确保不会同时触发多条规则；
3. 移除原函数中冗余的DISTINCT，因为逻辑调整后不会返回重复行。

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