如何计算mysql中两个表的值 [英] How to calculate the values from two tables in mysql

问题描述

我在这样的mysql中有一个表



表1名称：credit

 Date Company名称金额
 2013-12-11 smevc 500 
 2013-12-21 smevc 500 
 2013-12-21 smevc 1000 
 2013-12-21 smevc 6726 
 2013-12-21 smevc 8354.40 
 2013-12-20 smevc 700 
 2013-12-22 sec 3736 
 2013-12-23 ccs 3000 

表2名称：借方

日期company_name deb_amt 
 2013-12-24 smevc 1000 
 2013-12-22 smevc 2000 
 2013-12-22 ccs 2000 

现在我想在这里采取差值计算，即金额 - deb_amt

现在

我需要输出像

 comp_name bal_amt 
 smevc 14780.40 
 sec 3736 
 ccs 1000 

记住sec仅在表2中可用借记..不在表1中。

我得到了输出，但sec的值显示为null。

i使用了这个查询

  SELECT  cm.company_name  AS  comp_name，
（（ SELECT  SUM（credit_amount） FROM  credit_details c  WHERE  c.company_name = cm.company_name  GROUP   BY  c.company_name） -  
（ SELECT  SUM（debit_amount） FROM  debit_details d  WHERE  d.company_name = cm.company_name  GROUP   BY  d.company_name）） AS  bal_amt 
  FROM  credit_details cm  GROUP   BY  cm.company_name 
  ORDER   BY  bal_amt  DESC  

pls更正我的查询以获得输出。

谢谢，

Siva

解决方案

这不是一个优雅的解决方案但是它可能有用...

 选择 
 c .company，
 c.amount  -  ifnull（d.debit， 0 ）余额
 来自 
（
 选择 
公司，金额（金额）金额
 来自 credit 
  group   by  company 
）c 
  left   join  
（
 选择 
公司，sum（deb_amt）借记
 来自 debit 
  group   by  company 
）d 
  on  a.company = d.company 

I am having a table in mysql like this

Table1 name: credit

Date          Company Name        Amount
2013-12-11    smevc               500
2013-12-21    smevc               500
2013-12-21    smevc               1000
2013-12-21    smevc               6726
2013-12-21    smevc               8354.40
2013-12-20    smevc               700
2013-12-22    sec                 3736
2013-12-23    ccs                 3000

Table2 name: debit

Date         company_name         deb_amt
2013-12-24   smevc                1000
2013-12-22   smevc                2000
2013-12-22   ccs                  2000

Now i want to take difference calculation here i.e., Amount - deb_amt
Now
I need a output like

comp_name  bal_amt
smevc      14780.40
sec        3736
ccs        1000

Remember sec is available only in table 2 debit.. not in table 1 credit..
I got the output but the value of sec was displayed as null.
i used this query

SELECT cm.company_name AS comp_name,
((SELECT SUM(credit_amount) FROM credit_details c WHERE c.company_name = cm.company_name GROUP BY c.company_name) -
(SELECT SUM(debit_amount) FROM debit_details d WHERE d.company_name = cm.company_name GROUP BY d.company_name)) AS bal_amt
FROM credit_details cm GROUP BY cm.company_name
ORDER BY bal_amt DESC

pls correct my query to get the output.
Thanks,
Siva

解决方案

It's not an elegant solution but it may work...

select 
c.company,
c.amount - ifnull(d.debit,0) balance
from
(
select 
company,sum(amount) amount
from credit
group by company
)c
left join
(
select 
company, sum(deb_amt) debit
from debit
group by company
)d
on a.company = d.company

这篇关于如何计算mysql中两个表的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！