如何格式化查询以在两个MySQL select语句的结果之间进行算术运算

Question

我们知道如何进行简单的MySQL运算-例如：

mysql> select 10-7 as 'result';
+--------+
| result |
+--------+
|      3 |
+--------+

但是如果"；10〃；以及"；7〃；是MySQL选择查询的结果，我们如何计算？-例如：

select x.balance from (
  select sum(amount) as 'balance' 
  from table 
  where date between "2019-06-01" and "2019-06-30" 
  and type="cr"
) as x 
union 
select y.balance from (
  select sum(amount) as 'balance' 
  from table 
  where date between "2019-06-01" and "2019-06-30" 
  and type="dr"
) as y;
+---------+
| balance |
+---------+
| 5792.00 |
| 6014.26 |
+---------+

我如何将其全部写成一个查询来获得：

select 5792.00-6014.26 as 'result';
+---------+
| result  |
+---------+
| -222.26 |
+---------+

Answer 1

UNION将结果行追加到查询结果中。

您可以使用JOIN来附加列，但是使用稍微不同的查询会得到结果。

select sum(if(type='dr', amount, -amount)) as 'balance' 
  from table 
  where date between "2019-06-01" and "2019-06-30" 

在这里，我们使用IF函数来确定我们是在加还是在减这个量。

Answer 2

您可以尝试使用条件聚合函数SUM+CASE WHEN来进行算术。

select sum(CASE WHEN type = 'dr' THEN amount ELSE -amount END) as 'balance' 
from table 
where 
    date between "2019-06-01" and "2019-06-30" 
and 
    type IN ('dr','cr')