SQL WHERE子句中的CASE语句 [英] CASE Statement in SQL WHERE clause

问题描述

我正在尝试从在WHERE子句中使用CASE条件的表中获取数据，当前正在使用以下查询:-

I'm trying to fetch data from table where I'm using a CASE condition in the WHERE clause and currently I'm using following query:-

SELECT count(enq_id) AS total, sum(purchase_amount) AS purchase
FROM temp_stock
WHERE purchase_date <> '0000-00-00'
AND purchase_date < '2012-08-01'
AND (

STATUS = 'Sold'
OR STATUS = 'In Stock'
OR STATUS = 'Ref'
)
AND CASE WHEN (

STATUS = 'Sold'
)
THEN delivery_date >= '2012-08-01'
END

但是对于total返回0，对于purchase返回NULL.

But it returns 0 for total and NULL for purchase.

推荐答案

来自您的评论.

我想使用Case Statement，请您向我澄清where子句中的case stata

I want to use Case Statement, could u pls clarify me about case statament in where clause

您可以在WHERE中使用CASE语句，如下所示:

You can use CASE statement in WHERE like this:

SELECT count(enq_id) AS total, sum(purchase_amount) AS purchase
FROM temp_stock
WHERE purchase_date <> '0000-00-00'
AND purchase_date < '2012-08-01'
AND (    STATUS = 'Sold'
      OR STATUS = 'In Stock'
      OR STATUS = 'Ref')
AND CASE STATUS 
         WHEN 'Sold' 
         THEN delivery_date >= '2012-08-01'
         ELSE 1=1
    END

在这里您需要使用ELSE 1=1.否则，您将无法获得理想的结果.有关更多说明，请参见此SQLFiddle

Here you need to use ELSE 1=1. otherwise you will not get desired result. For more explanation see this SQLFiddle

这篇关于SQL WHERE子句中的CASE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！