如何在case语句中包含列名

I need to include a column name in the where clause of SQL query depending on a variable value. For Example, 1. If I have a variable,say,@test and I assigned the variable with the value 1/0. 2. I have a table ,say, table1 with 2 column names col1,col2.

我需要在SQL查询的where子句中根据变量值包含一个列名。例如,1。如果我有一个变量，比如@test我给变量赋值为1/0。2。我有一个表，表1有两个列名col1和col2。

Now, I am writing a query to fetch some data from the table by including only one column at a time depends on the @test variable(declared earlier) i.e. I need to include col1 in the where clause of the query if the @test is 1 and col2 if @test is 0.

现在,我写一个查询从表中获取一些数据,包括一次只一列取决于@test变量(早些时候宣布)。我需要在查询的where子句包含col1如果@test是1和col2 @test是0。

Please let me know the solution asap.

请尽快告诉我解决办法。

Thanks in advance

谢谢提前

3 个解决方案

#1

declare @tbl table
(
    col1 int,
    col2 int

)

insert into @tbl select 10, 20

declare @test int

set @test = 1

select *
from @tbl
Where Case When @test = 1 Then Col1 Else Col2 End  = 10

If datatype is different

如果数据类型是不同的

select *
from @tbl
Where Case when @test = 1 Then Col1 end = 10
        OR
      Case when @test = 2 Then Col2 end = 'sometext'

#2

Select *
From dbo.MyTable
Where Case When @Test = 1 Then Col1 Else Col2 End  > 100

#3

A slightly different approach:

一种稍微不同的方法:

Select *
From dbo.MyTable
Where Col1 = Case When @Test = 1 Then 10 Else Col1 End and
      Col2 = Case When @Test = 0 Then 'sometext' Else Col2 End

This version of the query may be sargable.

查询的这个版本可以被sargable。

#1

declare @tbl table
(
    col1 int,
    col2 int

)

insert into @tbl select 10, 20

declare @test int

set @test = 1

select *
from @tbl
Where Case When @test = 1 Then Col1 Else Col2 End  = 10