問題描述

我有一個表，其中的記錄結(jié)構(gòu)與此類似..

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

ID 角色I(xiàn)D
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".

有誰知道如何構(gòu)造一個選擇來只給我一條記錄?如果傳遞了 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 子句，因此將自身嵌套在自身內(nèi)部將是一個非常大的查詢.

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

編輯(根據(jù)收到的評論進(jìn)行編輯)
請注意，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.

這篇關(guān)于空 OR 匹配的 SQL 查詢 Where 子句(僅返回 1)?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！