Mysql刷题笔记 12.17 合并结果

T1217
+------+--------------+----------+--------+
| ID   | Type         | MasterID | Amount |
+------+--------------+----------+--------+
|    1 | 直接合同     |     NULL |   5000 |
|    2 | 补充合同     |        1 |   1000 |
|    3 | 补充合同     |        1 |    500 |
|    4 | 直接合同     |     NULL |   6000 |
|    5 | 直接合同     |     NULL |   4000 |
|    6 | 补充合同     |        5 |   1000 |
+------+--------------+----------+--------+

结果：
+------+--------------+--------+---------+
| ID   | Type         | amount | amount2 |
+------+--------------+--------+---------+
|    1 | 直接合同     |   5000 |    1500 |
|    4 | 直接合同     |   6000 |       0 |
|    5 | 直接合同     |   4000 |    1000 |
+------+--------------+--------+---------+
*/
-- 重点是ifnull
select a.ID, a.Type, a.Amount as amount,ifnull(b.amount2,0) as amount2 
from T1217 a left join
(select MasterID,sum(amount) as amount2 from T1217 group by MasterID) b
on b.MasterID = a.ID
where a.Type = '直接合同'