MySQL 查询 WHERE 包括 CASE 或 IF？

发布于 2024-08-28 19:01:36 字数 1188 浏览 6 评论 0原文

奇怪的问题。

我的查询看起来

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 或 bar 中有特定值，

我会尝试所有这些，但这需要大量时间完成该查询... :(

编辑：仅用于注释，我的问题是，我忘记了 CASE 中的 ELSE。

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

此解决方案也有效，但发布的一个更好...

原文

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 OR and not AND? Should my the second WHEN include brackets also, even when I only compare one field? Should I use IF and not CASE?

Basically I want to exclude some rows with specific values IF theres a specific value in field foo or 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 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

This solution works also, but the posted one is better...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浸婚纱 2024-09-04 19:01:36

您应该在这里使用 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)

You should use OR here instead of 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)

回复收藏 0 原文

~没有更多了~