mysql - 如何查询两个不同的mysql表并比较结果

Question

我有两个 Mysql 查询:

1:

select TblC.name, 
SUM(TblC.total) as Sum
from TblC
left join TblCli on TblC.id_cli = TblCli.id 
group by TblCli.name asc

结果是

 NAME     SUM
 john    1000
 peter    500
 Alicia   300

2:

select TblCli.name, 
SUM(TblRec.total) as Pay
from TblRec
left join TblCli on TblRec.id_cli = TblCli.id 
group by TblCli.name asc

结果是

NAME    Pay
john    500
peter   100

有两个不同的表，没有直接关系我需要加入这两个查询并得到如下结果:

NAME      SUM    PAY  Difference
john      1000   500         500
peter     500    100         400
Alicia    300    0           300

我怎样才能做到这一点？

Answer 1

以下内容应该有效。使用您的数据进行演示:http://sqlfiddle.com/#!9/ba6264/6

我强烈建议您通过 TblCli.id 而不是 TblCli.name 进行联接

select A.name, sum , IFNULL(pay,0) as pay, (sum - IFNULL(pay,0)) Difference FROM 
  (select TblC.name, 
  SUM(TblC.total) as Sum
  from TblC
  left join TblCli on TblC.id_cli = TblCli.id 
  group by TblCli.name) A
LEFT JOIN 
  (select TblCli.name, 
  SUM(TblRec.total) as Pay
  from TblRec
  left join TblCli on TblRec.id_cli = TblCli.id 
  group by TblCli.name) B
ON A.name = B.name;