“where”子句中的 SQL 开关/大小写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/206484/
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
SQL Switch/Case in 'where' clause
提问by Miles
I tried searching around, but I 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 在第一行 WHEN 上一直抱怨我的“=”...
How can I do this?
我怎样才能做到这一点？
回答by Bob Probst
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
回答by Lukek
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)
回答by Pittsburgh DBA
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
回答by Mark S. Rasmussen
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.
编辑：上述建议当然要好得多，请忽略我的。
回答by Dillie-O
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.
因此，在您的特定情况下，您需要将查询放入存储过程或创建三个单独的查询。
回答by atik sarker
OR operator can be alternative of case when in where condition
OR 运算符可以在 where 条件下替代 case
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
回答by Durre Najaf
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
回答by shah134pk
Try this:
尝试这个：
WHERE (
@smartLocationType IS NULL
OR account_location = (
CASE
WHEN @smartLocationType IS NOT NULL
THEN @smartLocationType
ELSE account_location
END
)
)
回答by kavitha Reddy
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;
回答by Mike Clark
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
