SQL Server 2008 中“WHERE”子句中的“CASE”语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8785209/
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
"CASE" statement within "WHERE" clause in SQL Server 2008
提问by user1018213
I am working with a query which contains "CASE" statement within "WHERE" clause. But SQL Server 2008 is giving some errors while executing it. Can anyone please help me with the correct query? Here is the query:
我正在处理一个在“WHERE”子句中包含“CASE”语句的查询。但是 SQL Server 2008 在执行时出现了一些错误。任何人都可以帮助我进行正确的查询吗？这是查询：
SELECT
tl.storenum 'Store #',
co.ccnum 'FuelFirst Card #',
co.dtentered 'Date Entered',
CASE st.reasonid
WHEN 1 THEN 'Active'
WHEN 2 THEN 'Not Active'
WHEN 0 THEN st.ccstatustypename
ELSE 'Unknown'
END 'Status',
CASE st.ccstatustypename
WHEN 'Active' THEN ' '
WHEN 'Not Active' THEN ' '
ELSE st.ccstatustypename
END 'Reason',
UPPER(REPLACE(REPLACE(co.personentered,'RT\\',''),'RACETRAC\\','')) 'Person Entered',
co.comments 'Comments or Notes'
FROM
comments co
INNER JOIN cards cc ON co.ccnum=cc.ccnum
INNER JOIN customerinfo ci ON cc.customerinfoid=ci.customerinfoid
INNER JOIN ccstatustype st ON st.ccstatustypeid=cc.ccstatustypeid
INNER JOIN customerstatus cs ON cs.customerstatuscd=ci.customerstatuscd
INNER JOIN transactionlog tl ON tl.transactionlogid=co.transactionlogid
LEFT JOIN stores s ON s.StoreNum = tl.StoreNum
WHERE
CASE LEN('TestPerson')
WHEN 0 THEN co.personentered = co.personentered
ELSE co.personentered LIKE '%TestPerson'
END
AND cc.ccnum = CASE LEN('TestFFNum')
WHEN 0 THEN cc.ccnum
ELSE 'TestFFNum'
END
AND CASE LEN('2011-01-09 11:56:29.327')
WHEN 0 THEN co.DTEntered = co.DTEntered
ELSE
CASE LEN('2012-01-09 11:56:29.327')
WHEN 0 THEN co.DTEntered >= '2011-01-09 11:56:29.327'
ELSE co.DTEntered BETWEEN '2011-01-09 11:56:29.327' AND '2012-01-09 11:56:29.327'
END
END
AND tl.storenum < 699
ORDER BY tl.StoreNum
回答by Code Magician
First off, the CASEstatement must be partof the expression, not the expression itself.
首先，CASE语句必须是表达式的一部分，而不是表达式本身。
In other words, you can have:
换句话说，您可以拥有：
WHERE co.DTEntered = CASE
WHEN LEN('blah') = 0
THEN co.DTEntered
ELSE '2011-01-01'
END
But it won't work the way you have written them eg:
但它不会像你写的那样工作，例如：
WHERE
CASE LEN('TestPerson')
WHEN 0 THEN co.personentered = co.personentered
ELSE co.personentered LIKE '%TestPerson'
END
You may have better luck using combined OR statements like this:
使用这样的组合 OR 语句可能会更好：
WHERE (
(LEN('TestPerson') = 0
AND co.personentered = co.personentered
)
OR
(LEN('TestPerson') <> 0
AND co.personentered LIKE '%TestPerson')
)
Although, either way I'm not sure how great of a query plan you'll get. These types of shenanigans in a WHEREclause will often prevent the query optimizer from utilizing indexes.
虽然，无论哪种方式，我都不确定您会得到多么好的查询计划。WHERE子句中的这些类型的诡计通常会阻止查询优化器使用索引。
回答by user1018213
This should solve your problem for the time being but I must remind you it isn't a good approach :
这应该暂时解决您的问题，但我必须提醒您这不是一个好方法：
WHERE
CASE LEN('TestPerson')
WHEN 0 THEN
CASE WHEN co.personentered = co.personentered THEN 1 ELSE 0 END
ELSE
CASE WHEN co.personentered LIKE '%TestPerson' THEN 1 ELSE 0 END
END = 1
AND cc.ccnum = CASE LEN('TestFFNum')
WHEN 0 THEN cc.ccnum
ELSE 'TestFFNum'
END
AND CASE LEN('2011-01-09 11:56:29.327')
WHEN 0 THEN CASE WHEN co.DTEntered = co.DTEntered THEN 1 ELSE 0 END
ELSE
CASE LEN('2012-01-09 11:56:29.327')
WHEN 0 THEN
CASE WHEN co.DTEntered >= '2011-01-09 11:56:29.327' THEN 1 ELSE 0 END
ELSE
CASE WHEN co.DTEntered BETWEEN '2011-01-09 11:56:29.327'
AND '2012-01-09 11:56:29.327'
THEN 1 ELSE 0 END
END
END = 1
AND tl.storenum < 699
回答by janak magotra
Try the following:
请尝试以下操作：
select * From emp_master
where emp_last_name=
case emp_first_name
when 'test' then 'test'
when 'Mr name' then 'name'
end
回答by Oleg Dok
I think that the beginning of your query should look like that:
我认为您的查询的开头应该是这样的：
SELECT
tl.storenum [Store #],
co.ccnum [FuelFirst Card #],
co.dtentered [Date Entered],
CASE st.reasonid
WHEN 1 THEN 'Active'
WHEN 2 THEN 'Not Active'
WHEN 0 THEN st.ccstatustypename
ELSE 'Unknown'
END [Status],
CASE st.ccstatustypename
WHEN 'Active' THEN ' '
WHEN 'Not Active' THEN ' '
ELSE st.ccstatustypename
END [Reason],
UPPER(REPLACE(REPLACE(co.personentered,'RT\\',''),'RACETRAC\\','')) [Person Entered],
co.comments [Comments or Notes]
FROM comments co
INNER JOIN cards cc ON co.ccnum=cc.ccnum
INNER JOIN customerinfo ci ON cc.customerinfoid=ci.customerinfoid
INNER JOIN ccstatustype st ON st.ccstatustypeid=cc.ccstatustypeid
INNER JOIN customerstatus cs ON cs.customerstatuscd=ci.customerstatuscd
INNER JOIN transactionlog tl ON tl.transactionlogid=co.transactionlogid
LEFT JOIN stores s ON s.StoreNum = tl.StoreNum
WHERE
CASE
WHEN (LEN([TestPerson]) = 0 AND co.personentered = co.personentered) OR (LEN([TestPerson]) <> 0 AND co.personentered LIKE '%'+TestPerson) THEN 1
ELSE 0
END = 1
AND
BUT
但
what is in the tail is completely not understandable
尾部是什么完全无法理解
回答by MnemosyneD
There WHEREpart could be written like this:
有WHERE部分可以这样写：
WHERE
(LEN('TestPerson') <> 0 OR co.personentered = co.personentered) AND
(LEN('TestPerson') = 0 OR co.personentered LIKE '%TestPerson') AND
(cc.ccnum = CASE LEN('TestFFNum')
WHEN 0 THEN cc.ccnum
ELSE 'TestFFNum'
END ) AND
(LEN('2011-01-09 11:56:29.327') <> 0 OR co.DTEntered = co.DTEntered ) AND
((LEN('2011-01-09 11:56:29.327') = 0 AND LEN('2012-01-09 11:56:29.327') <> 0) OR co.DTEntered >= '2011-01-09 11:56:29.327' ) AND
((LEN('2011-01-09 11:56:29.327') = 0 AND LEN('2012-01-09 11:56:29.327') = 0) OR co.DTEntered BETWEEN '2011-01-09 11:56:29.327' AND '2012-01-09 11:56:29.327' ) AND
tl.storenum < 699
回答by Anand
You could also try like below eg. to show only Outbound Shipments
您也可以尝试像下面这样，例如。仅显示出库货件
SELECT shp_awb_no,shpr_ctry_cd, recvr_ctry_cd,
CASE WHEN shpr_ctry_cd = record_ctry_cd
THEN "O"
ELSE "I"
END AS route
FROM shipment_details
WHERE record_ctry_cd = "JP"
AND "O" = CASE WHEN shpr_ctry_cd = record_ctry_cd
THEN "O"
ELSE "I"
END
回答by Singaravelan
Thanks for this question, actually I am looking for something else which is in below query. this may helps someone.
感谢您提出这个问题，实际上我正在寻找以下查询中的其他内容。这可能会帮助某人。
SELECT DISTINCT CASE WHEN OPPORTUNITY='' THEN '(BLANK)' ELSE OPPORTUNITY END
AS OPP,LEN(OPPORTUNITY) FROM [DBO].[TBL]
above query is to fill in dropdown which blank values shows as "(blank)". Also if we pass this value into sql where clause to get blank values with other values I don't know how to handle that. And finally came up with below solution this may helps to somebody.
上面的查询是填写下拉列表，其中空白值显示为“（空白）”。此外，如果我们将此值传递给 sql where 子句以获取具有其他值的空白值，我不知道如何处理。最后提出了以下解决方案，这可能对某人有所帮助。
here it is ,
这里是 ，
DECLARE @OPP TABLE (OPP VARCHAR(100))
INSERT INTO @OPP VALUES('(BLANK)'),('UNFUNDED'),('FUNDED/NOT COMMITTED')
SELECT DISTINCT [OPPORTUNITY]
FROM [DBO].[TBL] WHERE ( CASE WHEN OPPORTUNITY ='' THEN '(BLANK)' ELSE OPPORTUNITY END IN (SELECT OPP FROM @OPP))
ORDER BY 1
回答by user1780042
here is my solution
这是我的解决方案
AND CLI.PE_NOM Like '%' + ISNULL(@NomClient, CLI.PE_NOM) + '%'
Regads Davy
雷加兹·戴维
回答by user7640374
This works
这有效
declare @v int=A
select * from Table_Name where XYZ=202
and
dbkey=(case @v when A then 'Some Value 1'
else 'Some Value 2'
end)
回答by anurag
select TUM1.userid,TUM1.first_name + ' ' +TUM1.last_name as NAME,tum1.Business_Title,TUM1.manager_id,tum2.First_Name + ' ' + tum2.Last_Name as [MANAGER NAME],TUM1.project,TUM1.project_code,TUM1.rcc_code,TUM1.department,TCM.Company_Name,
case
when tum1.Gender_ID=1 then 'male'
else 'female'
end 'GENDER'
,tum1.Band as BAND,
case when tum1.Inactive=0 then 'STILL IN COMPANY'
else 'LEFT COMPANY'
end 'ACTIVE/INACTIVE'
from tbl_user_master TUM1
join tbl_Company_Master TCM on TCM.Company_Code=TUM1.Company_Code
join tbl_User_Master TUM2 on TUM1.Manager_ID=TUM2.UserID
where tum1.UserID in ('54545414')
