mysql 把表的值用来计算,如何从mysql中的两个表计算值

I am having a table in mysql like this

Table1 name: creditDate 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 sec 736

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 3000

ccs 1000

What query i need to use to get that output?

Thanks,

Siva

解决方案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]

For further information about JOIN's, please see: Visual Representation of SQL Joins[^]

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

I just remember you are using MySql, it will work now. Try it out at sqlfiddle[^].