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

问题描述

我在这样的mysql中有一个表



表1名称：credit

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

表2名称：借方

Table2 name: debit

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

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

现在

我需要输出像

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        3000
ccs        1000

我需要用什么查询来获得输出？

谢谢，

Siva

What query i need to use to get that output?
Thanks,
Siva

推荐答案

你可以在表之间使用JOIN

查看此链接了解更多信息



http：//dev.mysql。 com / doc / refman / 4.1 / en / join.html [ ^ ]

You can Use JOIN between tables
check this link for more info

http://dev.mysql.com/doc/refman/4.1/en/join.html[^]

试试这个：

Try this:

SELECT C.[Company Name] AS COMPNAME, SUM(C.Amount) AS AMT, SUM(D.DEB_AMT) AS DEBIT, SUM(C.Amount) - SUM(D.DEB_AMT) AS BALANCE
FROM Credit AS C INNER JOIN Debit AS D ON C.[Company Name] = D.[Company Name]
GROUP BY C.[Company Name]

有关JOIN的更多信息，请参阅： SQL连接的可视化表示 [ ^ ]

无需加入。

No need join.

SELECT cm.`Company Name` AS comp_name,
((SELECT SUM(Amount) FROM credit c WHERE c.`Company Name` = cm.`Company Name` GROUP BY c.`Company Name`) -
(SELECT SUM(deb_amt) FROM debit d WHERE d.company_name = cm.`Company Name` GROUP BY d.company_name)) AS bal_amt
FROM credit cm GROUP BY cm.`Company Name`
ORDER BY bal_amt DESC

我只记得您正在使用MySql，它现在可以使用了。在 sqlfiddle 上试试[ ^ ]。

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