可以处理 sql server 的 null 或 valued 日期范围的 sql 查询
it1352
帮助6人
问题说明
使用 SQL Server 2008.我有一个存储过程，它具有开始和结束日期作为日期范围的输入参数.
Using SQL Server 2008. I have a stored proc which has start and end date as input parameters for date range.
正在寻找一个 单一 sql 查询，它在 where 子句中具有开始和结束日期之间的日期，该查询可以处理日期均为空或两者都有值的两种情况.
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.
正确答案
#1
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结构.
If there is no index, then use IFNULL constructs proposed by others.
这篇好文章是转载于：学新通技术网
