MySQL 查询 WHERE 包括 CASE 或 IF？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2643783/
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
MySQL Query WHERE Including CASE or IF?
提问by handfix
Strange problem.
奇怪的问题。
My Query looks like
我的查询看起来像
SELECT DISTINCT ID, `etcetc`, `if/elses over muliple joined tables` FROM
table1 AS `t1`
# some joins, eventually unrelated in that context
WHERE
# some standard where statements, they work/
CASE
WHEN `t1`.`field` = "foo" THEN (`t1`.`anOtherField` != 123 AND `t1`.`anOtherField` != 456 AND `t1`.`anOtherOtherField` != "some String")
WHEN `t1`.`field` = "bar" THEN `t1`.`aSecondOtherField` != 12345
END
#ORDER BY CASE etc. Standard Stuff
Apperantly MySQL returns a wrong rowcount and I think my problem is in the logic of the WHERE ... CASE statement. Maybe with the brackets? Maybe I should go for operator ORand not AND? Should my the second WHENinclude brackets also, even when I only compare one field? Should I use IFand not CASE?
显然 MySQL 返回了错误的行数，我认为我的问题出在 WHERE ... CASE 语句的逻辑中。也许用括号？也许我应该去找运营商OR而不是AND？WHEN即使我只比较一个字段，我的第二个也应该包含括号吗？我应该使用IF而不是CASE吗？
Basically I want to exclude some rows with specific values IF theres a specific value in field fooor bar
基本上我想排除一些具有特定值的行，如果字段中有特定值foo或bar
I would try that all out, but it takes a huge amount of time to complete that query... :(
我会全力以赴，但完成该查询需要花费大量时间...... :(
Edit: Just for the notes, my problem was, that I forgot the ELSEin my CASE.
编辑：只是为了笔记，我的问题是，我忘记了ELSE我的CASE.
CASE
WHEN `t1`.`field` = "foo" THEN (`t1`.`anOtherField` != 123 AND `t1`.`anOtherField` != 456 AND `t1`.`anOtherOtherField` != "some String")
WHEN `t1`.`field` = "bar" THEN (`t1`.`aSecondOtherField` != 12345)
ELSE TRUE
END
This solution works also, but the posted one is better...
这个解决方案也有效，但发布的更好......
回答by newtover
You should use OR here instead of CASE:
您应该在这里使用 OR 而不是 CASE：
WHERE
(`t1`.`field` = "foo" AND `t1`.`anOtherField` != 123 AND `t1`.`anOtherField` != 456 AND `t1`.`anOtherOtherField` != "some String")
OR
(`t1`.`field` = "bar" AND `t1`.`aSecondOtherField` != 12345)
