一千萬個為什麽

搜索

MySQL Query WHERE包括CASE還是IF?

奇怪的問題。

我的查詢看起來像

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返回錯誤的rowcount,我認為我的問題在於WHERE ... CASE語句的邏輯。也許用括號?也許我應該去運營商 OR 而不是 AND ?我的第二個 WHEN 是否還包括括號,即使我只比較一個字段?我應該使用 IF 而不是 CASE 嗎?

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

這個解決方案也有效,但發布的更好......

最佳答案

你應該在這裏使用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)

轉載註明原文: MySQL Query WHERE包括CASE還是IF?