TSQL 条件 IS NULL 和 = 运算符

Question

我想在一份声明中写下这一点，该怎么做？

-- Is there a block for this user?
SELECT @BlockID = BlockID FROM [Blocks]  
WHERE UserID = @UserID

-- If not exists, get the default one
IF @BlockID IS NULL  
SELECT @BlockID = BlockID FROM [Blocks]  
WHERE UserID IS NULL

Answer 1

SET @BlockID = (SELECT TOP 1 BlockID
                FROM [Blocks]
                WHERE UserID IS NULL OR UserID = @UserID
                ORDER BY CASE WHEN UserID IS NOT NULL THEN 0 ELSE 1 END)

Answer 2

合并。它还可以轻松扩展到两个以上的情况。

select
  @BlockID = coalesce(u.BlockID, d.BlockID)
from
  Blocks d
  left join Blocks u on u.UserID = @UserID
where
  d.UserID is null

Answer 3

SET @BlockID = (SELECT BlockID FROM [Blocks] WHERE UserID IS NULL)