时间戳

首页 > 解决方案 > 如何使用 3 个表编写带有连接和条件总和的 mysql 查询

mysql - 如何使用 3 个表编写带有连接和条件总和的 mysql 查询

问题描述

我有 3 个保存发票、发票内容和付款的 mysql 表

 

select inv_head.invno,inv_head.invgtot,
sum(CASE WHEN payment.pay_method='CASH'  THEN payment.pay_amount ELSE 0 END) AS pay_cash,
sum(CASE WHEN payment.pay_method='CHEQUE'  THEN payment.pay_amount ELSE 0 END) AS pay_chq
from inv_head left JOIN payment on inv_head.invno=payment.invno   
where invdate between '2019-07-21 00:00:00' and '2019-07-21 23:00:00'  
and  unprodid=0
GROUP by inv_head.invno

返回以下哪个是正确的

invno           invgtot pay_cash    pay_chq 
20190721-173208-9   242.5   100     100 
20190721-174752-9   52  20      10  


select inv_head.invno,inv_head.invtot,inv_head.invgtot,
sum(CASE WHEN inv_body.typeid=1  THEN inv_body.line_tot ELSE 0 END) AS stdsaletot,
sum(CASE WHEN inv_body.typeid=2  THEN inv_body.line_tot ELSE 0 END) AS rettot,
sum(CASE WHEN inv_body.typeid=3  THEN inv_body.line_tot ELSE 0 END) AS dmgtot,
sum(CASE WHEN inv_body.typeid=4  THEN (inv_body.qty * inv_body.rate)ELSE 0 END) AS fitot
from inv_head,inv_body  
where invdate between '2019-07-21 00:00:00' and '2019-07-21 23:00:00'  
and  unprodid=0 and inv_head.invno=inv_body.invno 
GROUP by inv_head.invno

返回以下哪个是正确的

invno           invtot  invgtot stdsaletot  rettot  dmgtot  fitot   
20190721-173208-9   242.5   242.5   242.5       0   0   0   
20190721-174752-9   52  52  291     -168    -71 0

但是当我结合以上两个查询时，

select inv_head.invno,inv_head.invtot,inv_head.invgtot,
sum(CASE WHEN payment.pay_method='CASH'  THEN payment.pay_amount ELSE 0 END) AS pay_cash,
sum(CASE WHEN payment.pay_method='CHEQUE'  THEN payment.pay_amount ELSE 0 END) AS pay_chq,
sum(CASE WHEN inv_body.typeid=1  THEN inv_body.line_tot ELSE 0 END) AS stdsaletot,
sum(CASE WHEN inv_body.typeid=2  THEN inv_body.line_tot ELSE 0 END) AS rettot,
sum(CASE WHEN inv_body.typeid=3  THEN inv_body.line_tot ELSE 0 END) AS dmgtot,
sum(CASE WHEN inv_body.typeid=4  THEN (inv_body.qty * inv_body.rate)ELSE 0 END) AS fitot
from inv_body,inv_head left JOIN payment on inv_head.invno=payment.invno  
where invdate between '2019-07-21 00:00:00' and '2019-07-21 23:00:00'  
and  unprodid=0 and inv_head.invno=inv_body.invno 
GROUP by inv_head.invno

返回低于错误的结果

invno           invtot  invgtot pay_cash    pay_chq stdsaletot  rettot  dmgtot  fitot   
20190721-173208-9   242.5   242.5   100     100 727.5       0   0   0   
20190721-174752-9   52  52  80      40  873     -504    -213    0

标签： mysql

解决方案

您的加入在付款和 inv_head 上正常工作，但在 inv_body 上不起作用，因为您没有提供如何将 inv_body 与其他表加入 试试这个希望会对您有所帮助

SELECT inv_head.invno,inv_head.invtot,inv_head.invgtot, 
       SUM(CASE WHEN payment.pay_method='CASH' THEN payment.pay_amount ELSE 0 END) AS pay_cash, 
       SUM(CASE WHEN payment.pay_method='CHEQUE' THEN payment.pay_amount ELSE 0 END) AS pay_chq, 
       SUM(CASE WHEN inv_body.typeid=1 THEN inv_body.line_tot ELSE 0 END) AS stdsaletot, 
       SUM(CASE WHEN inv_body.typeid=2 THEN inv_body.line_tot ELSE 0 END) AS rettot, 
       SUM(CASE WHEN inv_body.typeid=3 THEN inv_body.line_tot ELSE 0 END) AS dmgtot,
       SUM(CASE WHEN inv_body.typeid=4 THEN (inv_body.qty * inv_body.rate)ELSE 0 END) AS fitot 
       FROM  payment RIGHT JOIN inv_head ON inv_head.invno=payment.invno
       WHERE invdate BETWEEN '2019-07-21 00:00:00' AND '2019-07-21 23:00:00'
         AND unprodid=0 LEFT JOIN inv_body ON inv_head.invno=inv_body.invno 
       GROUP BY inv_head.invno

推荐阅读