在where子句中如何使用案例 [英] How to use case in where clause

问题描述

我只想获取 status = 1 记录.但是我的表中没有状态列.因此，我使用 CASE ... WHEN ... THEN 导出了值.但是，当我尝试在where子句中使用case时，它显示语法错误.

I want to get only status = 1 records. But I don't have status column in my table. So I derived the value using CASE... WHEN... THEN. But when I try to use case in where clause, It shows syntax error.

我的查询

SELECT SQL_CALC_FOUND_ROWS *,
    CASE
        WHEN quantity > num_used AND (CURDATE() BETWEEN coupon_start_date AND coupon_end_date) THEN '1'
        ELSE '0'
    END AS STATUS
FROM
    table_coupon_code
WHERE
    (CASE
        WHEN quantity > num_used AND (CURDATE() BETWEEN coupon_start_date AND coupon_end_date) THEN '1'
        ELSE '0'
    END AS STATUS) = '1' AND coupon_status <> '2'

我该怎么做?

推荐答案

从where子句中删除AS STATUS

remove AS STATUS from where clause

SELECT SQL_CALC_FOUND_ROWS * ,
  CASE WHEN quantity > num_used AND (CURDATE( ) BETWEEN coupon_start_date AND coupon_end_date)
  THEN '1'
  ELSE '0'
  END AS STATUS
FROM table_coupon_code
WHERE 
CASE WHEN quantity > num_used AND (CURDATE( ) BETWEEN coupon_start_date AND coupon_end_date)
  THEN '1'
  ELSE '0'
  END = '1'
AND coupon_status <> '2'

但是您的CASE确实是不必要的.只需将CASE条件用作独立的WHERE条件，例如

But your CASE is really unnecessary. Just use your CASE condition as stand-alone WHEREcondition, like

[...]
WHERE quantity > num_used AND
CURDATE( ) BETWEEN coupon_start_date AND coupon_end_date AND
coupon_status <> '2'

这篇关于在where子句中如何使用案例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！