根据MySQL中的值选择字段

Question

有mysql表foo:

a | b | c
----------
1 | 2 | 0
2 | 3 | 1
3 | 4 | 0

和查询

SELECT a-b as subA, b-a as subB FROM foo;
subA | subB
-----------
-1  |   1
-1  |   1
-1  |   1

如果c=0，我如何选择a-b as sum，如果c=1，我怎么选择b-a as sum，这样我就得到了这个结果：

sum
---
-1
1
-1

Answer 1

SELECT (a-b)*(1-2*c) AS `sum`
FROM foo

Answer 2

您可以使用case表达式：

select f.*,
case c 
when 0 then a - b 
when 1 then b - a
end as res
from foo f

如果c总是0或1，我们可以对sign():有点兴趣

select f.*, sign(c - 0.5) * (b - a) as res 
from foo f

Answer 3

这很简单(请参阅SQLize.online(：

SELECT 
a-b as subA, 
b-a as subB,
CASE 
WHEN c = 0 THEN a-b
WHEN c = 1 THEN b-a
END as sum
FROM foo;

结果：

+======+======+=====+
| subA | subB | sum |
+======+======+=====+
| -1   | 1    | -1  |
+------+------+-----+
| -1   | 1    | 1   |
+------+------+-----+
| -1   | 1    | -1  |
+------+------+-----+