mysql - 如何在where子句中使用case

Question

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

我的问题

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'

我该怎么做？

Answer 1

从 where 子句中删除 AS STATUS

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 条件，例如

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