SQL Server 2008 中 "WHERE" 子句中的 "CASE" 语句

Question

SQL Server 2008 中 "WHERE" 子句中的 "CASE" 语句

141

我正在处理一个包含在“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

- user1018213

2

Case expression, not statement...（case表达式返回一个值。在存储过程中，case语句用于条件执行代码。） - jarlh

14个回答

23

请尝试以下方法：

SELECT * FROM emp_master 
WHERE emp_last_name= 
CASE emp_first_name 
 WHEN 'test'    THEN 'test' 
 WHEN 'Mr name' THEN 'name'
END

- janak magotra

16

这应该暂时解决了你的问题，但我必须提醒你这不是一个好的方法：

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

- user547541

8

我认为你的查询应该像这样开始：

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

但是

尾部的内容完全无法理解。

- Oleg Dok

4

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

- MnemosyneD

3

 select 
d.DISTNAME,e.BLKNAME,a.childid,a.studyingclass 
from Tbl_AdmissionRegister a
inner join District_master b on a.Schooid=b.Schooid
where 
case when len('3601')=4   then c.distcd 
           when len('3601')=6   then c.blkcd  
           when len('3601')=11  then c.schcd   end = '3601'

- Rambabu Bommedi

2

请提供更多信息，说明您的答案为什么有助于解决原始问题。仅发布代码可能对具有相同问题的用户来说有点模糊。 - Harry J

2

感谢您的提问，实际上我正在寻找以下查询中的其他内容。这可能有助于某些人。

      SELECT DISTINCT CASE WHEN OPPORTUNITY='' THEN '(BLANK)' ELSE OPPORTUNITY END
  AS OPP,LEN(OPPORTUNITY) FROM [DBO].[TBL]

上述查询是用来填充下拉列表的，其中空白值显示为"(blank)"。如果我们将该值传递到SQL的where子句中以获取空白值以及其他值，我不知道如何处理。最后，我想出了以下解决方案，这可能对某些人有帮助。

以下是解决方案:

 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

- Singaravelan

1

您也可以尝试以下示例，仅显示出站货运。

   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

- Anand

1

CASE LEN('TestPerson')
    WHEN 0 THEN co.personentered  = co.personentered ELSE co.personentered LIKE '%TestPerson'

尝试以下操作：

... and ( 
    (LEN('TestPerson') = 0 and co.personentered  = co.personentered) or
    (LEN('TestPerson') <> 0 and co.personentered LIKE '%TestPerson') ) and ...

- user1758901

0

我正在使用类似于这样的东西来根据字符串针和 UI 中的下拉菜单筛选用户。

where 
        u.username like '%' + isnull(@needle, '') + '%'
    and 1 =
    (
        case 
            when @status = 0            then 1 -- All in uu
            when @status = u.statusid   then 1 -- Special status 
            else 0 -- otherwise reject
        end
    )

- Stefan Michev

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Code Magician · Accepted Answer

首先，CASE语句必须是表达式的一部分，而不是表达式本身。

换句话说，你可以这样写：

WHERE co.DTEntered = CASE 
                          WHEN LEN('blah') = 0 
                               THEN co.DTEntered 
                          ELSE '2011-01-01' 
                     END

但是它不会按照你编写的方式工作，例如：

WHERE 
    CASE LEN('TestPerson')
        WHEN 0 THEN co.personentered  = co.personentered
   ELSE co.personentered LIKE '%TestPerson'
    END

您可以尝试使用组合的OR语句：

WHERE (
        (LEN('TestPerson') = 0 
             AND co.personentered = co.personentered
        ) 
        OR 
        (LEN('TestPerson') <> 0 
             AND co.personentered LIKE '%TestPerson')
      )

虽然无论如何，我都不确定你能得到多好的查询计划。在WHERE子句中进行这些诡计通常会阻止查询优化器利用索引。