Sql IN中的Sql Case语句 [英] Sql Case statement within Sql IN
问题描述
我有一个sql
语句,该语句具有如下所示的where条件
I have a sql
statement which have a where condition like below
WHERE (TABLE1.Field=@Id OR (@Id=0))
当@Id
值为零或更大时,此方法正常工作
This works fine when @Id
values is zero or more
现在,当@Id值变为0时,我需要匹配多个值.
Now I need to match multiple values when @Id value become 0.
我尝试了以下类似的操作,但不起作用.我知道我可以在这里使用IF-ELSE
,但是想知道是否可以使用case
statemet
I tried something like below which doesn't work. I know that I can use IF-ELSE
in here, but wonder whether this can be achieve using case
statemet
WHERE TABLE1.Field IN (CASE WHEN @Id>0 THEN @Id ELSE (6,16,18) END)
推荐答案
您可以使用OR
:
WHERE (@Id > 0 AND Table1.Field = @Id)
OR (@Id = 0 AND Table1.Field IN (6,16,18))
但是,我建议使用(正如您所说的)IF/ELSE
,当将两个这样的条件混合在一起时,您通常会强制执行次优计划.例如,在您的示例中,您可以将其简化为如下所示的模式:
However, I would advise using (as you have said) IF/ELSE
, when mashing together two conditions like this you can often force suboptimal plans. e.g In your example you could simplify this to a schema as follows:
CREATE TABLE T
( ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
Field INT NOT NULL,
SomeOtherField INT NULL
);
GO
INSERT T (Field)
SELECT Number
FROM Master..spt_values
CROSS JOIN (VALUES (1), (2), (3)) t (A)
WHERE Type = 'P'
GO
CREATE NONCLUSTERED INDEX IX_T_Field ON T (Field) INCLUDE (SomeOtherField);
这只是用数字0-2047填充其中一列,每列重复4次(仅用于某些示例数据).然后,如果我创建了两个过程,则一个使用"IF/ELSE",一个结合了上述标准:
This simply populates one of the columns with the numbers 0-2047 repeated 4 times each (just for some example data). Then If I create two procedures, one that uses 'IF/ELSE' one that combines the criteria as above:
CREATE PROCEDURE dbo.Test @ID INT
AS
SELECT ID, Field, SomeOtherField
FROM T
WHERE (@Id > 0 AND T.Field = @Id)
OR (@Id = 0 AND T.Field IN (6,16,18))
GO
CREATE PROCEDURE dbo.Test2 @ID INT
AS
IF @ID = 0
SELECT ID, Field, SomeOtherField
FROM T
WHERE T.Field IN (6, 16, 18)
ELSE
SELECT ID, Field, SomeOtherField
FROM T
WHERE T.Field = @Id
GO
由于查询的编译只会发生一次(除非您另外明确地声明),所以优化程序将不会选择其他计划,具体取决于您是向过程传递0还是将ID> 0传递给过程,因此以下两项:/p>
Since compilation of queries will only happen once (unless you explicitly say otherwise), the optimiser will not pick a different plan depending on whether you pass 0 or pass an ID > 0 to the procedure, so both of the following:
EXECUTE dbo.Test 0;
EXECUTE dbo.Test 1;
会给出这个计划:
第二个过程能够更好地估计最佳执行计划,因此运行以下命令:
The second procedure is able to estimate the best execution plan much better so running this:
EXECUTE dbo.Test2 0;
EXECUTE dbo.Test2 1;
给出以下计划:
现实世界中的示例显然会有所不同,我特意构建了一个证明我的观点的示例.使用IF/ELSE
来复制大量代码要花些力气,但这通常是值得的.
Real world examples will obviously vary, and I have deliberately constructed an example that proves my point. It is slightly more effort to duplicate a lot of code by using IF/ELSE
, but it is often worth it.
这篇关于Sql IN中的Sql Case语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!