mysql - UNION 查询中的部分加减法(减法)

Question

我现在有两个相似的表(一个用于账单，另一个用于支付)，我向用户展示了来自两者的联合混合数据..

Table Bills                             
CustomerId  Amount                      
1           100                        
2           100                        
1           100                        
2           100

Table Payments
CustomerId  Amount
1           100
2           100
1           100

现在我的用户可以看到以下信息

From Customer 1
Type    CustomerId   Amount
B       1            100
P       1            -100
B       1            100
P       1            -100
TOTAL 0

From Customer 2
Type    CustomerId   Amount
B       1            100
P       1            -100
B       1            100
Total 100

使用 UNION 语句一切正常

现在我需要在每条记录上显示部分余额，以便用户在查看记录时可以记住余额。

像这样...(期望的)

From Customer 1
Type    CustomerId   Amount    Partial
B       1            100       100
P       1            -100      0
B       1            100       100
P       1            -100      0

我已经尝试使用像@Partial := @Partial + Amount 这样的变量，但它似乎首先对第一部分(账单)求和，然后对其余部分(付款)求和......就像这样......

From Customer 1
Type    CustomerId   Amount    Partial
B       1            100       100
P       1            -100      200
B       1            100       200
P       1            -100      100

好像是先从 bills 算起，然后再做减法...有谁知道怎么解决吗？

******//更新//< strong>********

这里是原始查询...

(SELECT 'Bill' as cType , b.type, b.tal , 'Customer', b.number , b.date , b.subtot, b.tax, IF(b.type='CA' or b .type='CB' or b.type='CC' or b.type='CX',b.total*-1,b.total) as total FROM bills b WHERE b.idcustomer='000140') UNION ALL(SELECT 'Payment' as cType, 'CO' , '1' , '' , c.idcash , c.date , 0 ,0 , -c.amount FROM cash c WHERE c.idcustomer='000140' and ( c. type='CO' 或 c.type='DM') ) 按日期升序排列；

这带来了这样的东西

Bill FX 1 客户 9 2011-02-25 0.00 0.00 100.00

付款 CO 1 37 2011-03-04 0.00 0.00 -100.00

Bill FX 1 客户 616 2011-03-23 0.00 0.00 100.00

付款 CO 1 751 2011-04-12 0.00 0.00 -100.00

Bill FX 1 客户 1267 2011-04-27 0.00 0.00 100.00

付款 CO 1 1157 2011-05-10 0.00 0.00 -100.00

Bill FX 1 客户 1974 2011-05-26 0.00 0.00 100.00

付款 CO 1 1654 2011-06-08 0.00 0.00 -100.00

然后当我尝试对 patiars 求和时...使用以下代码

设置@running_total=0；(SELECT 'Bill' as cType , b.type, b.tal , 'Customer', b.number , b.date , b.subtot, b.tax, IF(b.type='CA' or b.type= 'CB' or b.type='CC' or b.type='CX',b.total*-1,b.total) as total, ( @running_total := @running_total + total) AS RunningTotal FROM bills b WHERE b.idcustomer='000140') 联合所有(SELECT 'Payment' as cType, 'CO' , '1' , '' , c.idcash , c.date , 0 ,0 , -c.amount, ( @running_total := @running_total-c.amount) AS RunningTotal FROM cash c WHERE c.idcustomer='000140' and ( c.type='CO' or c.type='DM') ) order by date asc;

结果...

Bill FX 1 客户 9 2011-02-25 0.00 0.00 100.00 100.00

付款 CO 1 37 2011-03-04 0.00 0.00 -100.00 1905.00

Bill FX 1 客户 616 2011-03-23 0.00 0.00 100.00 200.00

付款 CO 1 751 2011-04-12 0.00 0.00 -100.00 1805.00

Bill FX 1 客户 1267 2011-04-27 0.00 0.00 100.00 300.00

付款 CO 1 1157 2011-05-10 0.00 0.00 -100.00 1705.00

如您所见，似乎首先从账单中求和，然后从付款中开始减去......

Answer 1

以下是我能想到的在 MySQL 中执行此操作的唯一方法，假设您有用于排序记录的时间戳。如果您只有中等数量的数据，这可能会证明效率太低:

select t.*,
       ((select sum(amount)
         from bills b
         where b.customerId = t.customerId and
               b.datetime <= t.datetime
        ) -
        (select sum(amount)
         from payments p
         where p.customerid = t.customerid and
               p.datetime <= t.datetime
        )
       ) as balance
from ((select 'B' as type, customerid, amount, datetime from bills b) union all
      (select 'P', customerid, amount, datetime from payments p)
     ) t