logo标签列表

SQL Server中CASE .. WHEN .. IN语句的含义

sqlsql-serversql-server-2005case-when
36

我正在尝试在 SQL Server 2005 中查询以下选择语句：

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

它返回以下错误

Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'IN'.

请给我一些建议，说一下我的代码可能出了什么问题。

5个回答
46
谢谢，我已经修复了它，现在看起来像下面这样。 - Faisal
14

尝试这个...

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

只需移除高亮字符串

SELECT AlarmEventTransactionTableTable.TxnID, CASE AlarmEventTransactions.DeviceID WHEN DeviceID IN('7', '10', '62', '58', '60', ...)

我已经将fixit设为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。 - Faisal
13

谢谢答案，我已将语句修改为以下形式

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
4
可能更容易阅读，如果使用“简单案例”手写，例如：
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

...... 这让我觉得你也许会从一个查找表中受益，你可以将其 JOIN 联结起来，以完全消除 CASE 表达式。

0
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接