SQL基于可空外键关联表Enabled字段的分场景过滤实现方案

过滤逻辑实现 #

直接在WHERE子句中组合逻辑判断即可实现需求，相比CASE语句更简洁高效，完整查询代码如下：

SELECT [p].[Id]
FROM [dbo].[SomeTable] AS [p]
LEFT JOIN [dbo].[Table1] AS [t1] ON [t1].[Id] = [p].[T1_Id]
LEFT JOIN [dbo].[Table2] AS [t2] ON [t2].[Id] = [p].[T2_Id]
LEFT JOIN [dbo].[Table3] AS [t3] ON [t3].[Id] = [p].[T3_Id]
WHERE [p].[Id] = SomeId
  -- 核心过滤逻辑
  AND ([p].[T1_Id] IS NULL OR [t1].[Enabled] = 1)
  AND ([p].[T2_Id] IS NULL OR [t2].[Enabled] = 1)
  AND ([p].[T3_Id] IS NULL OR [t3].[Enabled] = 1)
ORDER BY [p].[Id]

条件说明 #

• T1校验规则：[p].[T1_Id] IS NULL OR [t1].[Enabled] = 1，外键为空时跳过校验，非空时要求关联主表记录必须启用，完全覆盖你提到的两个场景要求
• T2、T3校验规则：无论T1是否为空，只要对应外键非空就校验关联记录的启用状态，和需求描述完全匹配

CASE写法参考 #

如果你需要使用CASE语句实现，等价写法如下：

WHERE [p].[Id] = SomeId
  AND CASE WHEN [p].[T1_Id] IS NOT NULL THEN [t1].[Enabled] ELSE 1 END = 1
  AND CASE WHEN [p].[T2_Id] IS NOT NULL THEN [t2].[Enabled] ELSE 1 END = 1
  AND CASE WHEN [p].[T3_Id] IS NOT NULL THEN [t3].[Enabled] ELSE 1 END = 1

结果验证 #

以上逻辑匹配你提供的测试数据，最终返回的Id为2, 3, 4, 7, 8，和预期结果完全一致。

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