奇怪的问题。
我的查询看起来像
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
显然 MySQL 返回了错误的行计数,我认为我的问题出在 WHERE ... CASE 语句的逻辑中。也许用括号?也许我应该去找接线员OR
并不是AND
?我应该第二个WHEN
即使我只比较一个字段,也包括括号吗?我应该使用IF
并不是CASE
?
基本上我想排除一些具有特定值的行如果字段中有特定值foo
or bar
我会尝试一切,但是需要花费大量时间才能完成该查询...:(
编辑:只是为了笔记,我的问题是,我忘记了ELSE
in my 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
该解决方案也有效,但发布的解决方案更好......
您应该在这里使用 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)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)