SQL tsql 查询中 where 子句中的 CASE 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25872741/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CASE statement in where clause in tsql query
提问by Barzul
I'm trying to write a case statement in the where clause for a query I am working on. I am importing the code in Crystal reports and I am basically trying to say if the variable 'type' is set to 'create' run for this date range in the where clause else run for a different date range. It keeps giving me an error. I can't seem to identify what is wrong with my syntax here. Help anyone?
我正在尝试在 where 子句中为我正在处理的查询编写一个 case 语句。我正在导入 Crystal 报告中的代码，我基本上是想说明变量“type”是否设置为“create”，在 where 子句中针对不同的日期范围运行此日期范围。它一直给我一个错误。我似乎无法在这里确定我的语法有什么问题。帮助任何人？
DECLARE @Date1 DATETIME
DECLARE @Date2 DATETIME
DECLARE @type VARCHAR(20)
SET @Date1 = '2010-1-1'
SET @Date2 = '2010-2-1'
SET @type = '{?DateType}'
select *
from filled
WHERE
(CASE WHEN @type = 'create' THEN
filled.CREATEDON >= @Date1
AND filled.CREATEDON < DATEADD(d, +1, @Date2)
WHEN @type <> 'create' THEN
filled.datefilled >= @Date1
AND filled.datefilled < DATEADD(d, +1, @Date2)
END)
回答by Gordon Linoff
You don't need a casestatement
你不需要case声明
WHERE ( (@type = 'create' and filled.CREATEDON >= @Date1 AND filled.CREATEDON < DATEADD(d, +1, @Date2) ) or
(@type <> 'create' and filled.datefilled >= @Date1 AND filled.datefilled < DATEADD(d, +1, @Date2) )
)
This leaves the non-sensical logic you have in the casestatement. Both conditions have the same value for @type. I assume that is a typo.
这留下了您在case语句中的无意义逻辑。两个条件对 具有相同的值@type。我认为这是一个错字。
回答by GarethD
Using a CASE expression in a where clause is possible, but generally it can be avoided, and rewritten using AND/OR, IN your case it would be:
在 where 子句中使用 CASE 表达式是可能的，但通常可以避免，并使用AND/OR, 在您的情况下重写：
WHERE( @Type = 'create'
AND filled.CREATEDON >= @Date1
AND filled.CREATEDON < DATEADD(d, +1, @Date2)
)
OR ( @Type != 'create'
AND filled.datefilled >= @Date1
AND filled.datefilled < DATEADD(d, +1, @Date2)
)
HOWEVERqueries like this usually produce suboptimal plans. You should use IF/ELSElogic if possible:
然而，像这样的查询通常会产生次优计划。IF/ELSE如果可能，您应该使用逻辑：
IF @Type = 'create'
BEGIN
SELECT *
FROM Filled
WHERE Filled.CreatedOn >= @Date1
AND Filled.CreatedOn < DATEADD(DAY, 1, @Date2)
END
ELSE
BEGIN
SELECT *
FROM Filled
WHERE Filled.DateFilled >= @Date1
AND Filled.DateFilled < DATEADD(DAY, 1, @Date2)
END
The reason for this is the value of @type is not known at compile time, therefore the optimiser does not know whether it will need to search on DateFilledor CreatedOn, therefore cannot plan to use an index on either column (if one exists), so will do a table scan regardless of the indexes available. Whereas if you separate the logic with IF/ELSEit does not matter what the value of @type is, a plan is created for each branch of the IF, and in each branch the optimiser knows which column will be searched, and can plan to use the appropriate index.
这样做的原因是编译时不知道 @type 的值，因此优化器不知道它是否需要搜索DateFilled或CreatedOn，因此无法计划在任一列上使用索引（如果存在），因此将无论可用的索引如何，都进行表扫描。而如果将逻辑与IF/ELSE@type 的值无关，则会为 的每个分支创建一个计划IF，并且在每个分支中，优化器都知道将搜索哪一列，并且可以计划使用适当的索引.
You can also use UNION ALL:
您还可以使用UNION ALL：
SELECT *
FROM Filled
WHERE Filled.CreatedOn >= @Date1
AND Filled.CreatedOn < DATEADD(DAY, 1, @Date2)
AND @Type = 'create'
UNION ALL
SELECT *
FROM Filled
WHERE Filled.DateFilled >= @Date1
AND Filled.DateFilled < DATEADD(DAY, 1, @Date2)
AND @Type <> 'create';
Again, if indexes exist on DateFilledor CreatedOnthis is much more likely to produce a plan that uses them than using OR.
同样，如果索引存在于DateFilledorCreatedOn这更有可能产生使用它们的计划而不是使用OR.
