英文:
Conditional WHERE clause based on variable
问题
在select * from #t查询的WHERE子句中，你可以使用CASE/WHEN/END语句来处理这3种情况，如下所示：
select *
from #t
where
(
@cucu = 'a' and p = 0
)
OR
(
@cucu = 'b' and p = 1
)
OR
(
@cucu not in ('a', 'b')
)
这个查询会根据不同的@cucu值来处理三种情况：如果@cucu是'a'，它将选择p等于0的行；如果@cucu是'b'，它将选择p等于1的行；如果@cucu既不是'a'也不是'b'，它将选择所有行。
英文:
I have something like this:
declare @cucu varchar(10)
select @cucu = 'c'
create table #t(id int, title varchar(50), p bit)
insert into #t(id, title, p) values (1, 'alpha', 0)
insert into #t(id, title, p) values (2, 'beta', 1)
insert into #t(id, title, p) values (3, 'gamma', 0)
if (@cucu = 'a')
begin
select *
from #t
where p = 0
end
else if (@cucu = 'b')
begin
select *
from #t
where p = 1
end
else
begin
select *
from #t
end
drop table #t
Is there a way to have those 3 cases treated in a CASE/WHEN/END on the WHERE of the select * from #t query somehow?
If I create an other variable, like this:
declare @b bit; select @b = 0; if @cucu = 'b' select @b = 1
then the first 2 cases are simple to have in one query:
select *
from #t
where p = @b
But for the 3rd possibility, I don't know how to do this
答案1
得分: 3
你需要使用 OR 和 AND 子句。你可能还想在 OPTION 子句中包括 RECOMPILE，因为我假设这个查询将针对一个更大的表运行，这三个查询的查询计划可能会有很大的不同：
SELECT id,
title,
p
FROM #t
WHERE (p = 0 AND @cucu = 'a')
OR (p = 1 AND @cucu = 'b')
OR (@cucu NOT IN ('a','b')) --假设 @cucu 不会有 NULL 值
OPTION (RECOMPILE);
英文:
You would need to use OR and AND clauses. You'd also likely want to include a RECOMPILE in the OPTION clause, as the query plans for the 3 queries could be quite different (as I assume this would be run against a much larger table):
SELECT id,
title,
p
FROM #t
WHERE (p = 0 AND @cucu = 'a')
OR (p = 1 AND @cucu = 'b')
OR (@cucu NOT IN ('a','b')) --Assumes @cucu isn't going to have a NULL value
OPTION (RECOMPILE);
答案2
得分: 1
根据我的看法，我认为您正在尝试根据变量'@cucu'的值执行SELECT语句。
以下是我在假设您在SQL Server Management Studio (SSMS) 中工作时针对您的问题使用CASE/WHEN的方法。
DECLARE @cucu varchar(10)
SET @cucu = 'c'
SELECT * FROM #t WHERE (CASE WHEN @cucu = 'a' THEN 0 WHEN @cucu = 'b' THEN 1 ELSE p END) = p
注意：上述代码示例中的#t是一个占位符，您需要将其替换为实际的表名。
英文:
In my perspective, I think you are trying to execute SELECT statement based on the value of variable '@cucu'.
Here is my approach with CASE/WHEN for your problem assuming you are working in SSMS.
DECLARE @cucu varchar(10)
SET @cucu = 'c'
SELECT * FROM #t WHERE (CASE WHEN @cucu = 'a' THEN 0 WHEN @cucu = 'b' THEN 1 ELSE p END) = p
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论