SQL Server CASE .. WHEN .. IN 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6042767/
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 Server CASE .. WHEN .. IN statement
提问by Faisal
On SQL server 2005 I am trying to query this select statement
在 SQL Server 2005 上，我试图查询这个 select 语句
SELECT AlarmEventTransactionTableTable.TxnID,
CASE AlarmEventTransactions.DeviceID
WHEN DeviceID IN( '7', '10', '62', '58',
'60', '46', '48', '50',
'137', '139', '142', '143', '164' )
THEN '01'
WHEN DeviceID IN( '8', '9', '63', '59',
'61', '47', '49', '51',
'138', '140', '141', '144', '165' )
THEN '02'
ELSE 'NA'
END AS clocking,
AlarmEventTransactionTable.DateTimeOfTxn
FROM multiMAXTxn.dbo.AlarmEventTransactionTable
It returns the error below
它返回以下错误
Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'IN'.
消息 156，级别 15，状态 1，第 4 行 关键字“IN”附近的语法不正确。
Please give me some advice on what could be wrong with my code.
请给我一些关于我的代码可能有什么问题的建议。
回答by Martin Smith
CASE AlarmEventTransactions.DeviceIDshould just be CASE.
CASE AlarmEventTransactions.DeviceID应该只是CASE。
You are mixing the 2 forms of the CASEexpression.
您正在混合表达式的 2 种形式CASE。
回答by Faisal
Thanks for the Answer I have modified the statements to look like below
感谢您的回答我已将语句修改为如下所示
SELECT
AlarmEventTransactionTable.TxnID,
CASE
WHEN DeviceID IN('7', '10', '62', '58', '60',
'46', '48', '50', '137', '139',
'141', '145', '164') THEN '01'
WHEN DeviceID IN('8', '9', '63', '59', '61',
'47', '49', '51', '138', '140',
'142', '146', '165') THEN '02'
ELSE 'NA' END AS clocking,
AlarmEventTransactionTable.DateTimeOfTxn
FROM
multiMAXTxn.dbo.AlarmEventTransactionTable
回答by Vinay Kumar
Try this...
尝试这个...
SELECT
AlarmEventTransactionTableTable.TxnID,
CASE
WHEN DeviceID IN('7', '10', '62', '58', '60',
'46', '48', '50', '137', '139',
'142', '143', '164') THEN '01'
WHEN DeviceID IN('8', '9', '63', '59', '61',
'47', '49', '51', '138', '140',
'141', '144', '165') THEN '02'
ELSE 'NA' END AS clocking,
AlarmEventTransactionTable.DateTimeOfTxn
FROM
multiMAXTxn.dbo.AlarmEventTransactionTable
Just remove highlighted string
只需删除突出显示的字符串
SELECT AlarmEventTransactionTableTable.TxnID, CASE AlarmEventTransactions.DeviceIDWHEN DeviceID IN('7', '10', '62', '58', '60', ...)
SELECT AlarmEventTransactionTableTable.TxnID, CASE AlarmEventTransactions.DeviceIDWHEN DeviceID IN('7', '10', '62', '58', '60', ...)
回答by onedaywhen
It might be easier to read when written out in longhand using the 'simple case' e.g.
使用“简单案例”以手写方式写出时可能更容易阅读，例如
CASE DeviceID
WHEN '7 ' THEN '01'
WHEN '10 ' THEN '01'
WHEN '62 ' THEN '01'
WHEN '58 ' THEN '01'
WHEN '60 ' THEN '01'
WHEN '46 ' THEN '01'
WHEN '48 ' THEN '01'
WHEN '50 ' THEN '01'
WHEN '137' THEN '01'
WHEN '139' THEN '01'
WHEN '142' THEN '01'
WHEN '143' THEN '01'
WHEN '164' THEN '01'
WHEN '8 ' THEN '02'
WHEN '9 ' THEN '02'
WHEN '63 ' THEN '02'
WHEN '59 ' THEN '02'
WHEN '61 ' THEN '02'
WHEN '47 ' THEN '02'
WHEN '49 ' THEN '02'
WHEN '51 ' THEN '02'
WHEN '138' THEN '02'
WHEN '140' THEN '02'
WHEN '141' THEN '02'
WHEN '144' THEN '02'
WHEN '165' THEN '02'
ELSE 'NA'
END AS clocking
...which kind makes me thing that perhaps you could benefit from a lookup table to which you can JOINto eliminate the CASEexpression entirely.
...哪种让我觉得也许您可以从查找表中受益，您可以完全JOIN消除该CASE表达式。
