我有2个表AmountIn
和AmountOut
。
第一个表Amountin
看起来像:
AmountIn
+--------+--------------+-----------+
| account| date | AmountIn |
+--------+--------------+-----------+
| A | 2017/2/6 | 200 |
| A | 2017/2/5 | 100 |
| A | 2017/2/5 | 500 |
| B | 2017/2/1 | 1000 |
| B | 2017/2/1 | 2000 |
| C | 2017/1/20 | 25 |
+--------+----+---------+-----------+
第二个看起来像:
AmountOut
+--------+--------------+-----------+
| account| date |AmountOut |
+--------+--------------+-----------+
| A | 2017/2/8 | 200 |
| A | 2017/2/7 | 100 |
| A | 2017/2/6 | 500 |
| B | 2017/2/2 | 1000 |
| B | 2017/2/1 | 2000 |
| C | 2017/1/20 | 25 |
+--------+----+---------+-----------+
现在我想要一个查询,将显示结果如下:
ForAccountA
+--------+--------------+----------+-----------+------------+
| account| date | AmountIn | AmountOut | Balancy |
+--------+--------------+-------- -+-----------+------------+
| A | 2017/2/5 | 500 | 0 | 500 |
| A | 2017/2/5 | 100 | 0 | 600 |
| A | 2017/2/6 | 0 | 500 | 100 |
| A | 2017/2/6 | 200 | 0 | 300 |
| A | 2017/2/7 | 0 | 100 | 200 |
| A | 2017/2/8 | 0 | 200 | 0 |
+--------+----+---------+----------+-----------+------------+
查询中的
date field
是两个表中日期的并集,并且平衡计算为:last balance + AmountIn - AmounOut
最佳答案
尝试这个:
select
t.*,
@sum := if(@account = account,
@sum + AmountIn - AmountOut,
if((@account := account) is not null,
AmountIn - AmountOut, 0)
) balance
from (
select
*
from (
select
1 x,
account,
date,
AmountIn,
0 AmountOut
from AmountIn
union all
select
0 x,
account,
date,
0 AmountIn,
AmountOut
from AmountOut
) t order by account, date, x
) t cross join (select @account := null, @sum := 0) t2
编辑:
对于三个表:
select
t.*,
@sum := if(@account = account,
@sum + amountOne + amountTwo - amountThree,
if((@account := account) is not null,
amountOne + amountTwo - amountThree, 0)
) balance
from (
select
*
from (
select
2 x, account, date, amount amountOne,
0 amountTwo, 0 amountThree
from table1
union all
select
1 x, account, date, 0 amountOne,
amount amountTwo, 0 amountThree
from table2
union all
select
0 x, account, date, 0 amountOne,
0 amountTwo, amount amountThree
from table3
) t order by account, date, x
) t cross join (select @account := null, @sum := 0) t2
关于mysql - SQL联合或联接或两者一起,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/42115591/