問題描述
使用 SQL Server 2008.我有一個存儲過程，它具有開始和結(jié)束日期作為日期范圍的輸入?yún)?shù).
Using SQL Server 2008. I have a stored proc which has start and end date as input parameters for date range.
正在尋找一個 單一 sql 查詢，它在 where 子句中具有開始和結(jié)束日期之間的日期，該查詢可以處理日期均為空或兩者都有值的兩種情況.
Looking for a single sql query which has a between start and end date in the where clause which can handle both cases where the dates are either both null or both have values.
我不想使用 IF 語句.
I don't want to use an IF statement.
推薦答案
WITH limits AS
(
SELECT COALESCE(@startDate, MIN(mydate)) AS startDate, COALESCE(@endDate, MAX(mydate)) AS endDate
FROM mytable
)
SELECT m.*
FROM limits
JOIN mytable m
ON mydate BETWEEN startDate AND endDate
如果在 mydate 上有索引，這將是最有效的，因為這個條件是 sargable 并且將使用 Index Seek.
This will be most efficient if there is an index on mydate, since this condition is sargable and will use an Index Seek.
如果沒有索引，則使用其他人提出的IFNULL結(jié)構(gòu).
If there is no index, then use IFNULL constructs proposed by others.
這篇關(guān)于一個可以處理 sql server 中的 null 或 valued 日期范圍的 sql 查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！