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

Question

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

• inv_head(invno,invtot,invgtot)
• inv_body(invno,typeid,数量,费率,linetot)
• 付款(invno、pay_amount、pay_method)

 

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

Answer 1

您的联接在 payment 和 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