SQL Server按优先级查询指定列是否存在指定值的实现咨询

实现逻辑说明 #

• 需求为按照 [Actual Key] > [Confirmed Key] > [Present Key] 的层级优先级匹配目标值X，仅返回最高优先级命中的结果集，无匹配则返回空
• 示例中使用表名dbo.KeyRecords，可替换为你的实际表名；@TargetKey为查询目标值变量，可替换为实际查询值

方案1：全局层级匹配（高优先级列存在X即返回该列所有匹配行） #

适用于：只要X在[Actual Key]列出现过，不管其他列有没有X，都返回[Actual Key] = X的所有行；无匹配再查下一级

DECLARE @TargetKey VARCHAR(100) = '替换为你的查询值X';

-- 优先级1：匹配Actual Key列
SELECT * FROM dbo.KeyRecords
WHERE [Actual Key] = @TargetKey

UNION ALL

-- 优先级2：仅当Actual Key无匹配时执行
SELECT * FROM dbo.KeyRecords
WHERE [Confirmed Key] = @TargetKey
AND NOT EXISTS (SELECT 1 FROM dbo.KeyRecords WHERE [Actual Key] = @TargetKey)

UNION ALL

-- 优先级3：仅当Actual和Confirmed都无匹配时执行
SELECT * FROM dbo.KeyRecords
WHERE [Present Key] = @TargetKey
AND NOT EXISTS (SELECT 1 FROM dbo.KeyRecords WHERE [Actual Key] = @TargetKey)
AND NOT EXISTS (SELECT 1 FROM dbo.KeyRecords WHERE [Confirmed Key] = @TargetKey);

方案2：行级仅匹配（仅某行的X仅出现在当前优先级列才命中） #

适用于：需要某行的X仅存在于当前优先级列，另外两个列都不为X时才返回该行，无符合条件的行再查下一级

DECLARE @TargetKey VARCHAR(100) = '替换为你的查询值X';

SELECT * FROM dbo.KeyRecords
WHERE 
-- 优先级1：仅Actual Key为X
([Actual Key] = @TargetKey AND [Confirmed Key] <> @TargetKey AND [Present Key] <> @TargetKey)
-- 优先级2：仅Confirmed Key为X，且无优先级1的匹配行
OR (
    [Confirmed Key] = @TargetKey AND [Actual Key] <> @TargetKey AND [Present Key] <> @TargetKey
    AND NOT EXISTS (SELECT 1 FROM dbo.KeyRecords WHERE [Actual Key] = @TargetKey AND [Confirmed Key] <> @TargetKey AND [Present Key] <> @TargetKey)
)
-- 优先级3：仅Present Key为X，且无优先级1、2的匹配行
OR (
    [Present Key] = @TargetKey AND [Actual Key] <> @TargetKey AND [Confirmed Key] <> @TargetKey
    AND NOT EXISTS (SELECT 1 FROM dbo.KeyRecords WHERE [Actual Key] = @TargetKey AND [Confirmed Key] <> @TargetKey AND [Present Key] <> @TargetKey)
    AND NOT EXISTS (SELECT 1 FROM dbo.KeyRecords WHERE [Confirmed Key] = @TargetKey AND [Actual Key] <> @TargetKey AND [Present Key] <> @TargetKey)
);

注意事项 #

• 列名包含空格，必须使用方括号[]包裹避免语法错误
• 可根据X的实际数据类型调整@TargetKey的声明类型，比如数值类型改为INT、BIGINT等
• 上述语句完全兼容SQL Server 2019版本，可直接在SSMS 18中运行测试

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