MySQL部门用户人员求和,MySQL的加入和求和

Please look to this picture

i want to get

sum of money in table come

sum of money in table leave

inner join emp using id

Thats my query

SELECT uid,SUM(money) FROM come

INNER JOIN emp ON(come.uid = emp.id)

WHERE emp.statue=1

GROUP BY come.uid

UNION

SELECT uid,SUM(money) FROM `leave`

INNER JOIN emp ON ( leave.uid = emp.id )

GROUP BY leave.uid

and the result was this image

解决方案

Another way to do it (not tested, so let me know if it blows up):

SELECT emp.id, IF(emp.statue=1, c.sumCome, 0) AS sumCome, l.sumLeave

FROM emp

LEFT JOIN (SELECT uid, SUM(money) AS sumCome

FROM come

GROUP BY uid

) c ON emp.id = c.uid

LEFT JOIN (SELECT uid, SUM(money) AS sumLeave

FROM leave

GROUP BY uid

) l ON emp.id = l.uid

I don't know which'll be faster compared to Kevin's. You might want to explain them both and see.