来来来，老有关问题，新方案，高分求sql_Sql Server

来来来，老问题，新方案，高分求sql

SQL code

表A：ID      Qty10      0101     100102     20020      0 201     500表B：ID      ParentID101     10102     10201     20 现在需要得出如下结果：ID      Qty10      300101     100102     20020      500201     500

意思就是查询结果中，10是等于101和102的总和，20等于201的总和？
求sql
越简单越好

------解决方案--------------------

SQL code

-- 建表create table ta(id int, qty int);insert into ta select 10,0 union allselect 101,100 union allselect 102,200 union allselect 20,0  union allselect 201,500;create table tb(ID int, ParentID int);insert into tb select 101,10 union allselect 102,10 union allselect 201,20;-- 查询select id, qty=case when (select COUNT(*) from tb where tb.ParentID=ta.id)>0     then isnull((        select sum(c.qty) from ta c where c.id in(select id from tb where parentid=ta.id)         ),0)    else qty endfrom ta-- 结果10    300101    100102    20020    500201    500
------解决方案--------------------

select a.id,Sum(Case when b.ParentID Is Null then a.qty else c.qty End) from ta a
left outer join tb b on a.id=b.ParentID
left outer join ta c on b.id=c.id
Group by a.id