mysql - 查询对两个表求和

Question

查询两个表的总和

我有一个交易表和一个支付表。我需要在 Trans Table 和 Payment Table 上获取总金额，并且它必须按 TransID 分组。我希望有人可以为此提供正确的 sql 语句帮助我。

我尝试了下面的 sql，但是 Trans Table 上的金额总和将乘以 Payment 表上相同 TransID 的记录数。

-> Select Trans.TransID, sum(Trans.Amount), sum(Payment.Amount) 
   from Trans
   Left Join Payment on Payment.TransID = Trans.TransID

我也试过下面的sql，但是处理时间比上面的sql语句长。

-> Select TD.TransID, TD.Amt, PD.paid 
   from
   (
       Select Trans.TransID, Sum(Trans.Amount) AS Amt 
       from Trans Group By Trans.TransID
   ) AS TD 
   Left Join
   (
       Select Payment.TransID, sum(Payment.Amount) AS Paid 
       from payment 
       Group by Payment.TransID
   ) AS PD On PD.TransID = TD.TransID

转表

TransID |   Amount   
T1      |   10    
T2      |   15    
T3      |   12    
T4      |   20    
T5      |   11    
T1      |   15    
T5      |   14


----------

支付表

TransID |   Amount    
T1      |   5    
T3      |   10          
T1      |   3    
T2      |   5    
T5      |   10


----------

我需要的查询结果应该是这样的:

TransID  |  Amount  |   Paid    
T1       |  25      |   8    
T2       |  15      |   5    
T3       |  12      |   10    
T4       |  20      |   0   
T5       |  25      |   10

Answer 1

您可以使用两种不同的查询，一种是对所有 Amounts 求和(并将 Paid 列设置为 0)，另一种是对所有 Paid 值求和(并将 Amount 列设置为 0)并使用 UNION ALL 查询和 SUM结果:

SELECT TransID, SUM(Amount) AS Amount, SUM(Paid) AS Paid
FROM (
  SELECT TransID, SUM(Amount) AS Amount, 0 AS Paid
  FROM Trans
  GROUP BY TransID
  UNION ALL
  SELECT TransID, 0 AS Amount, SUM(Amount) AS Paid
  FROM Payment
  GROUP BY TransID
) s
GROUP BY TransID

请看 fiddle here .