联表查询求和再算差值,求SQL

联表查询求和再算差值,求SQL
统计表1和表2相同客户号,然后求和,再对比表3金额差值

img

img

先将表1和表2数据合并,之后分组聚合算出总金额,之后select想要的结果字段即可

SELECT 
    t3.cust_no,t3.amt,t2.cust_no,t2.amt,t3.amt-t2.amt as balance
FROM    
    (SELECT 
        客户号 AS cust_no,
        SUM(余额) AS amt 
     FROM tb3 
     GROUP BY 
         客户号) t3
LEFT JOIN(
    SELECT  
        客户号 AS cust_no,SUM(余额) AS amt 
    FROM 
        (SELECT 客户号,余额 from tb1 union all SELECT 客户号,余额 from tb2) t
    GROUP BY 
        t.客户号) t2
ON
  t3.cust_no = t2.cust_no