MySQL 如何在 where 子句中使用 case
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6812276/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use case in where clause
提问by Gowri
I want to get only status = 1records. 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.
我只想获得状态 = 1条记录。但是我的表中没有状态列。所以我使用CASE... WHEN... THEN得出了这个值。但是当我尝试在 where 子句中使用 case 时，它显示语法错误。
my query
我的查询
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'
How can I do this ?
我怎样才能做到这一点 ？
回答by rabudde
remove AS STATUSfrom where clause
AS STATUS从 where 子句中删除
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'
But your CASEis really unnecessary. Just use your CASEcondition as stand-alone WHEREcondition, like
但是你CASE的真的没必要。只需将您的CASE条件用作独立WHERE条件，例如
[...]
WHERE quantity > num_used AND
CURDATE( ) BETWEEN coupon_start_date AND coupon_end_date AND
coupon_status <> '2'
回答by Olaf
If you do not want to repeat the case statement you could wrap the select in a subselect or make a view. Subselect is something like
如果您不想重复 case 语句，您可以将选择包装在一个子选择中或创建一个视图。子选择是这样的
select status
from (select case
when zip like '4321%' then 1 else 0 end as status
from adr
) t
where status = 1;
回答by Adam F
Nobody Suggested the HAVING Clause?
没有人建议使用 HAVING 子句？
This allows you to query the selected columns, instead of the actual results. Great for using case and function calls.
这允许您查询选定的列，而不是实际结果。非常适合使用案例和函数调用。
回答by ypercube??
I suppose you have some other, more complicated query, as the one you have provided is equivalent to:
我想您还有其他一些更复杂的查询，因为您提供的查询相当于：
SELECT SQL_CALC_FOUND_ROWS * ,
'1' AS STATUS
FROM table_coupon_code
WHERE quantity > num_used
AND CURDATE() BETWEEN coupon_start_date AND coupon_end_date
AND coupon_status <> '2'
