Mysql Join和求和 [英] Mysql Join AND SUM

问题描述

请看这张图片

http://i.stack.imgur.com/A2RB2.jpg

我想得到

1. 表中的钱总和来
2. 请假假的金额
3. 使用id的内部加入emp

那是我的查询

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 

结果是这张图片

http://i.stack.imgur.com/xZtO3.jpg

推荐答案

另一种方法(未经测试，所以请告诉我它是否爆炸了):

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

我不知道哪一种会比凯文的更快.您可能要同时explain将它们都看到.

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

这篇关于Mysql Join和求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！