mysql - 如何在 mysql 查询中显示零值？

Question

此代码对 a 和 b 进行操作，并检查结果是否等于 c。如果是，就打印这一行。我的问题是如何更改此代码以显示结果 (a/b) = 0 时的行。

SELECT id, a, b, operation, c
FROM expressions
where
case
    when operation LIKE '+' AND (a + b = c) then c
    when operation LIKE '-' AND (a - b = c) then c 
    when operation like '/' AND (a / b = c) then c
    when operation like '*' AND (a * b = c) then c
ELSE FALSE END;

输出:

id  a   b   operation   c
1   2   3   +   5
4   4   7   *   28

11  0   1   /   0
14  239 0   *   0
15  18  18  -   0

1、2行就ok了，打印出来了。应该打印 3、4、5 行，但实际上没有!当 a/b = 0 时，sql 查询中的第二个条件为 false - 未打印行，例如0/1 = 0。它是 0，应该打印出来。与不应打印的 1/0 相反。我的解决方案是将 (a/b = c) 转换为 unsigned 但它不起作用？

Answer 1

您不应该混合使用 boolean 和 int 这样的类型，因为会发生隐式转换。

使用显式值而不是 TRUE/FALSE(0 被视为 FALSE)。

SELECT id, a, b, operation, c
FROM expressions
where
case
    when operation LIKE '+' AND (a + b = c) then 1
    when operation LIKE '-' AND (a - b = c) then 1 
    when operation like '/' AND (a / b = c) then 1
    when operation like '*' AND (a * b = c) then 1
ELSE 0 END = 1;

或者:

SELECT id, a, b, operation, c
FROM expressions
where
case
    when operation LIKE '+' AND (a + b = c) then TRUE
    when operation LIKE '-' AND (a - b = c) then TRUE
    when operation like '/' AND (a / b = c) then TRUE
    when operation like '*' AND (a * b = c) then TRUE
ELSE FALSE END;