SQL Switch/Case in where子句

I tried searching around but couldn't find anything that would help me out.

我试着四处搜寻，但找不到任何能帮助我的东西。

I'm trying to do this in SQL:

我试着用SQL来做这个:

declare @locationType varchar(50);
declare @locationID int;

SELECT column1, column2
FROM viewWhatever
WHERE
CASE @locationType
    WHEN 'location' THEN account_location = @locationID
    WHEN 'area' THEN xxx_location_area = @locationID
    WHEN 'division' THEN xxx_location_division = @locationID

I know that I shouldn't have to put '= @locationID' at the end of each one but I can't get the syntax even close to being correct. SQL keeps complaining about my '=' on the first WHEN line...

我知道，我不需要在每个语句的末尾都加上'= @locationID'，但我甚至无法得到接近正确的语法。SQL一直在抱怨我的“=”。

Help!

的帮助!

10 个解决方案

#1

declare @locationType varchar(50);
declare @locationID int;

SELECT column1, column2
FROM viewWhatever
WHERE
@locationID = 
  CASE @locationType
      WHEN 'location' THEN account_location
      WHEN 'area' THEN xxx_location_area 
      WHEN 'division' THEN xxx_location_division 
  END

#2

without a case statement...

没有一个case语句……

SELECT column1, column2
FROM viewWhatever
WHERE
    (@locationType = 'location' AND account_location = @locationID)
    OR
    (@locationType = 'area' AND xxx_location_area = @locationID)
    OR
    (@locationType = 'division' AND xxx_location_division = @locationID)

#3

Here you go.

给你。

SELECT
   column1, 
   column2
FROM
   viewWhatever
WHERE
CASE 
    WHEN @locationType = 'location' AND account_location = @locationID THEN 1
    WHEN @locationType = 'area' AND xxx_location_area = @locationID THEN 1
    WHEN @locationType = 'division' AND xxx_location_division = @locationID THEN 1
    ELSE 0
END = 1

#4

I'd say this is an indicator of a flawed table structure. Perhaps the different location types should be separated in different tables, enabling you to do much richer querying and also avoid having superfluous columns around.

我认为这是一个有缺陷的表结构的指示器。也许不同的位置类型应该在不同的表中分开，使您可以进行更丰富的查询，并且避免出现多余的列。

If you're unable to change the structure, something like the below might work:

如果你无法改变结构，如下所示可能会奏效:

SELECT
    *
FROM
    Test
WHERE
    Account_Location = (
        CASE LocationType
          WHEN 'location' THEN @locationID
          ELSE Account_Location
        END
    )
    AND
    Account_Location_Area = (
        CASE LocationType
          WHEN 'area' THEN @locationID
          ELSE Account_Location_Area
        END
    )

And so forth... We can't change the structure of the query on the fly, but we can override it by making the predicates equal themselves out.

等等……我们不能动态地改变查询的结构，但可以通过使谓词本身相等来覆盖它。

EDIT: The above suggestions are of course much better, just ignore mine.

编辑:以上建议当然更好，忽略我的。

#5

The problem with this is that when the SQL engine goes to evaluate the expression, it checks the FROM portion to pull the proper tables, and then the WHERE portion to provide some base criteria, so it cannot properly evaluate a dynamic condition on which column to check against.

这样做的问题是，当SQL引擎开始评估表达式时，它会检查FROM部分以提取适当的表，然后检查WHERE部分以提供一些基本条件，因此它不能正确地评估要检查哪个列的动态条件。

You can use a WHERE clause when you're checking the WHERE criteria in the predicate, such as

当您检查谓词中的WHERE条件时，您可以使用WHERE子句，例如。

WHERE account_location = CASE @locationType
                              WHEN 'business' THEN 45
                              WHEN 'area' THEN 52
                         END

so in your particular case, you're going to need put the query into a stored procedure or create three separate queries.

因此，在特定情况下，需要将查询放入存储过程中，或者创建三个单独的查询。

#6

OR operator can be alternative of case when in where condition

或操作者可在任何情况下替代情况

ALTER PROCEDURE [dbo].[RPT_340bClinicDrugInventorySummary]
    -- Add the parameters for the stored procedure here
     @ClinicId BIGINT = 0,
     @selecttype int,
     @selectedValue varchar (50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
    drugstock_drugname.n_cur_bal,drugname.cdrugname,clinic.cclinicname

FROM drugstock_drugname
INNER JOIN drugname ON drugstock_drugname.drugnameid_FK = drugname.drugnameid_PK
INNER JOIN drugstock_drugndc ON drugname.drugnameid_PK = drugstock_drugndc.drugnameid_FK
INNER JOIN drugndc ON drugstock_drugndc.drugndcid_FK = drugndc.drugid_PK
LEFT JOIN clinic ON drugstock_drugname.clinicid_FK = clinic.clinicid_PK

WHERE   (@ClinicId = 0 AND 1 = 1)
    OR  (@ClinicId != 0 AND drugstock_drugname.clinicid_FK = @ClinicId)

    -- Alternative Case When You can use OR
    AND ((@selecttype = 1 AND 1 = 1)
    OR  (@selecttype = 2 AND drugname.drugnameid_PK = @selectedValue)
    OR  (@selecttype = 3 AND drugndc.drugid_PK = @selectedValue)
    OR  (@selecttype = 4 AND drugname.cdrugclass = 'C2')
    OR  (@selecttype = 5 AND LEFT(drugname.cdrugclass, 1) = 'C'))

ORDER BY clinic.cclinicname, drugname.cdrugname
END

#7

Please try this query. Answer To above post:

请试试这个查询。上面的答案:

select @msgID, account_id
    from viewMailAccountsHeirachy
    where 
    CASE @smartLocationType
        WHEN 'store' THEN account_location
        WHEN 'area' THEN xxx_location_area 
        WHEN 'division' THEN xxx_location_division 
        WHEN 'company' THEN xxx_location_company 
    END  = @smartLocation

#8

Try this:

试试这个:

WHERE (
    @smartLocationType IS NULL 
    OR account_location = (
         CASE
            WHEN @smartLocationType IS NOT NULL 
                 THEN @smartLocationType
            ELSE account_location 
         END
    )
)

#9

Try this query. Its very easy to understand:

试试这个查询。它很容易理解:

CREATE TABLE PersonsDetail(FirstName nvarchar(20), LastName nvarchar(20), GenderID int);
GO

INSERT INTO PersonsDetail VALUES(N'Gourav', N'Bhatia', 2),
              (N'Ramesh', N'Kumar', 1),
              (N'Ram', N'Lal', 2),
              (N'Sunil', N'Kumar', 3),
              (N'Sunny', N'Sehgal', 1),
              (N'Malkeet', N'Shaoul', 3),
              (N'Jassy', N'Sohal', 2);
GO

SELECT FirstName, LastName, Gender =
    CASE GenderID
    WHEN 1 THEN 'Male'
    WHEN 2 THEN 'Female'
    ELSE 'Unknown'
    END
FROM PersonsDetail

#10

Case Statement in SQL Server Example

Syntax

CASE [ expression ]

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

Example

SELECT contact_id,
CASE website_id
  WHEN 1 THEN 'TechOnTheNet.com'
  WHEN 2 THEN 'CheckYourMath.com'
  ELSE 'BigActivities.com'
END
FROM contacts;

OR

SELECT contact_id,
CASE
  WHEN website_id = 1 THEN 'TechOnTheNet.com'
  WHEN website_id = 2 THEN 'CheckYourMath.com'
  ELSE 'BigActivities.com'
END
FROM contacts;