空 OR 匹配的 SQL 查询 Where 子句(仅返回 1)?

2021-09-10 00:00:00 sql tsql sql-server

我有一个表，其中的记录结构与此类似..

I have a table that has records with a structure similar to this..

ID 角色ID
1 空
2 15
3 16

ID RoleID
1 NULL
2 15
3 16

我写了一个 where 子句来获取如下记录

I wrote a where clause to get records like the following

SELECT * from TableX 
WHERE (RoleID = 2 OR RoleID IS NULL)

这让我得到了 "1,NULL" 的记录

This gets me the record of "1,NULL"

但是如果我查询

SELECT * from TableX 
WHERE (RoleID = 15 OR RoleID IS NULL)

我得到1,NULL"和2,15".

I get back "1,NULL" and "2,15".

有谁知道如何构造一个选择来只给我一条记录?如果传递了 15，我只想要2,15"，如果没有匹配，我只想要1,NULL".

Does anyone know how to structure a select to give me only one record? I only want "2,15" if 15 was passed and "1,NULL" if there are no matches.

请注意，实际查询包含更多的 where 子句，因此将自身嵌套在自身内部将是一个非常大的查询.

Note, the actual query has MANY more where clauses to it, so nesting itself inside itself would be a very big query.

推荐答案

SELECT TOP 1 with ORDER BY RoleID DESC 怎么样

How about SELECT TOP 1 with ORDER BY RoleID DESC

这是一个工作示例.

declare @mytable table
(
    ID int null,
    RoleID int null
)
insert @mytable values
(1, null),
(2, 15),
(3, 1)

select TOP 1 * 
from @mytable 
WHERE (RoleID = 2 OR RoleID IS NULL)
order by RoleID desc


select top 1 * from @mytable 
WHERE (RoleID = 15 OR RoleID IS NULL)
order by RoleID desc

编辑(根据收到的评论进行编辑)
请注意，Insert 语句仅适用于 SQL Server 2008.对于 2008 之前的版本，您必须将其拆分为单独的插入.

Edit (edited based on comments received)
Note that the Insert statement works only for SQL Server 2008. For versions prior to 2008, you will have to break it into invidual inserts.

相关文章