mysql - 如何在MySQL中正确使用IN？

Question

我有一个非常复杂的查询需求，现在简单使用两个表作为演示:

mysql> select * from user_table;
+--------+------+------+
| name   | dep  | uid  |
+--------+------+------+
| frank  | IT   | 1001 |
| jack   | IT   | 1002 |
| Sissel | FA   | 1003 |
| Li     | IT   | 1004 |
| Mok    | PM   | 1005 |
+--------+------+------+

第二个:

mysql> select * from money_log;
+------+-------+
| uid  | money |
+------+-------+
| 1001 |  9989 |
| 1001 |    89 |
| 1001 |   189 |
| 1002 |   389 |
+------+-------+

然后我尝试通过以下方式总结金钱:

SELECT uid, sum(money) as moneys
FROM money_log
WHERE uid in (
    SELECT uid 
    FROM user_table
    WHERE dep='IT'
) 
GROUP BY uid;

结果如下。

+------+--------+
| uid  | moneys |
+------+--------+
| 1001 |  10267 |
| 1002 |    389 |
+------+--------+

我期望的是:

+------+--------+
| uid  | moneys |
+------+--------+
| 1001 |  10267 |
| 1002 |    389 |
| 1004 |    0   |
+------+--------+

有什么好的办法吗？

Answer 1

改用LEFT JOIN

select u.uid, coalesce(sum(m.money),0) as money
from user_table u
left join money_log m on u.uid = m.uid  
where u.dep = 'IT'
group by u.uid

注意:确保在每个列引用中包含表别名。