这样的sql语句应该怎样写

[quansui]

create table a
(
id int,
je1 money,
je2 money,
je3 money,
je4 money,
je5 money,
je6 money,
je7 money,
primary key(id)
)
create table b
(
id int,
aid int,
iType varchar(10),
je money,
primary key(id)
)
--
insert a select 1,0,0,0,0,0,0,0
union select 2,0,0,0,0,0,0,0
union select 3,0,0,0,0,0,0,0
union select 4,0,0,0,0,0,0,0
union select 5,0,0,0,0,0,0,0
union select 6,0,0,0,0,0,0,0
union select 7,0,0,0,0,0,0,0
insert b select 1,1,'type1',10
union select 2,1,'type2',10.5
union select 3,1,'type2',1
union select 4,2,'type1',2
union select 5,1,'type5',12
union select 6,3,'type1',13
union select 7,1,'type2',14
union select 8,2,'type3',15
union select 9,3,'type5',16
union select 10,4,'type6',17
union select 11,5,'type7',18
union select 12,5,'type2',19
union select 13,1,'type3',19
union select 14,7,'type4',10
union select 15,1,'type5',11
union select 16,6,'type6',111

现在想写一条更新表a的语句，要求把表b里的je合计按照表a的id和表b的aid对应，按表b的类型不同填到表a的不同字段里
例如表b的type1就填到表a的je1里
想了半天也没有想出来简单的

update a
set je1=(select sum(je) from b where a.id=b.aid and b.iType='type1')
,je2=(select sum(je) from b where a.id=b.aid and b.iType='type2')
,je3=(select sum(je) from b where a.id=b.aid and b.iType='type3')
,je4=(select sum(je) from b where a.id=b.aid and b.iType='type4')
,je5=(select sum(je) from b where a.id=b.aid and b.iType='type5')
,je6=(select sum(je) from b where a.id=b.aid and b.iType='type6')
,je7=(select sum(je) from b where a.id=b.aid and b.iType='type7')
select * from a
感觉这么写太笨了，请高手看看

[卖水果的net]

-- 写成这们的，只要扫描一次表 b 就可以了
with m as (
select 
    aid, 
    sum(case when iType='type1' then je else 0 end) as je1,
    sum(case when iType='type2' then je else 0 end) as je2,
    sum(case when iType='type3' then je else 0 end) as je3,
    sum(case when iType='type4' then je else 0 end) as je4,
    sum(case when iType='type5' then je else 0 end) as je5,
    sum(case when iType='type6' then je else 0 end) as je6,
    sum(case when iType='type7' then je else 0 end) as je7
from b 
group by aid
)
update a 
set je1 = m.je1, je2 = m.je2, je3 = m.je3, je4 = m.je4, je5 = m.je5, je6 = m.je6, je7 = m.je7
from m 
where a.id = m.aid 
go

[道素]

你也可以用转置的方法

UPDATE aa SET aa.je1=cc.type1,aa.je2=cc.type2,aa.je3=cc.type3,aa.je4=cc.type4,aa.je5=cc.type5,aa.je6=cc.type6,aa.je7=cc.type7 
FROM a AS aa
INNER JOIN (
    select * from
   (SELECT aid,iType,je FROM b) bb
    PIVOT(SUM(je) FOR iType IN (type1,type2,type3,type4,type5,type6,type7)) u
) cc ON aa.id=cc.aid