SQL条件逻辑异常排查：员工类型为1时未返回折扣表NULL值记录

嘿，我看到你遇到的问题了，咱们一步步来搞定它！

首先，先指出你现有代码里的两个问题：

1. 变量赋值的语法错误：你在查询EMPLOYEE表时用了AND而不是WHERE，这会导致变量@employee_type无法正确赋值，修正后的赋值语句应该是这样的：

   DECLARE @employee_type smallint
   SET @employee_type = (SELECT employee_type FROM [dbo].[EMPLOYEE] (nolock) WHERE employee_stub = @employee_stub)
   

2. WHERE条件的逻辑错误：你原来的条件(@employee_type IS NULL AND @employee_type = 1)永远不可能成立——一个变量不可能同时是NULL和1，这就是为什么当@employee_type=1时，无法匹配到discount_employee_type为NULL的记录。

修正后的完整查询语句 #

咱们调整WHERE条件的逻辑，直接对应你的需求：当员工类型是2时只匹配类型2的折扣；当员工类型是1时，匹配类型1或者NULL的折扣。代码如下：

DECLARE @employee_type smallint
-- 修正语法错误：将AND改为WHERE
SET @employee_type = (SELECT employee_type FROM [dbo].[EMPLOYEE] (nolock) WHERE employee_stub = @employee_stub)

SELECT * 
FROM [dbo].[DISCOUNT] (nolock) 
WHERE acct_id = @acct_id 
AND (
    -- 员工类型为2时，只匹配折扣类型2
    (@employee_type = 2 AND discount_employee_type = 2)
    OR
    -- 员工类型为1时，匹配折扣类型1或NULL
    (@employee_type = 1 AND (discount_employee_type = 1 OR discount_employee_type IS NULL))
)

更简洁的写法 #

如果你想让代码更紧凑，也可以用下面的逻辑——当员工类型是1时额外允许NULL，其他情况只匹配对应类型：

DECLARE @employee_type smallint
SET @employee_type = (SELECT employee_type FROM [dbo].[EMPLOYEE] (nolock) WHERE employee_stub = @employee_stub)

SELECT * 
FROM [dbo].[DISCOUNT] (nolock) 
WHERE acct_id = @acct_id 
AND (
    discount_employee_type = @employee_type
    OR
    (@employee_type = 1 AND discount_employee_type IS NULL)
)

这样修改后，当@employee_type=1时，就会同时返回discount_employee_type为1和NULL的记录；当@employee_type=2时，只会返回类型2的折扣，完全符合你的需求啦！

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