你还应该有个部门表吧。create table #depart ( dpt varchar(20)) --部门表
create table #Fee ( id int,Amount decimal(8,2),Alldpt varchar(200)) --表A
create table #B (id int identity(1,1),Amount decimal(8,2),dpt varchar(20)) --表Binsert #depart select 'IT'
union select 'HR'
union select 'FIN'
union select 'ADMIN'
union select 'Sales'
union select 'Marketing'
union select 'Qulity'insert #Fee select 1,1000,'IT,HR,FIN'
union select 2,2000,'ADMIN,FIN'
union select 3,3000,'Sales,Marketing,Qulity'select r=identity(int,1,1),id,amount,dpt into #tmp
from #Depart a join #Fee b on charindex(','+dpt+',',','+alldpt+',')>0update #tmp set amount=amount /( select count(*) from #tmp where id=a.id) from #tmp a update #tmp set amount=b.amount-(select sum(amount) from #tmp where id=a.id and r<a.r)
from #tmp a join #fee b on a.id=b.id
where r in ( select max(r) from #tmp group by id)insert #B (amount,dpt) select amount,dpt from #tmpdrop table #depart,#fee,#b,#tmp
create table #Fee ( id int,Amount decimal(8,2),Alldpt varchar(200)) --表A
create table #B (id int identity(1,1),Amount decimal(8,2),dpt varchar(20)) --表Binsert #depart select 'IT'
union select 'HR'
union select 'FIN'
union select 'ADMIN'
union select 'Sales'
union select 'Marketing'
union select 'Qulity'insert #Fee select 1,1000,'IT,HR,FIN'
union select 2,2000,'ADMIN,FIN'
union select 3,3000,'Sales,Marketing,Qulity'select r=identity(int,1,1),id,amount,dpt into #tmp
from #Depart a join #Fee b on charindex(','+dpt+',',','+alldpt+',')>0update #tmp set amount=amount /( select count(*) from #tmp where id=a.id) from #tmp a update #tmp set amount=b.amount-(select sum(amount) from #tmp where id=a.id and r<a.r)
from #tmp a join #fee b on a.id=b.id
where r in ( select max(r) from #tmp group by id)insert #B (amount,dpt) select amount,dpt from #tmpdrop table #depart,#fee,#b,#tmp
create table tb(ID int,金额 int,部门 varchar(50))
insert tb select 1,1000,'IT,HR,FIN'
union all select 2,2000,'ADMIN,FIN'
union all select 3,3000,'Sales,Marketing,Qulity'
go--处理
declare @i int
select @i=max(len(部门)) from tb
set rowcount @i
select id=identity(int) into #t from syscolumns a,syscolumns b
set rowcount 0select id=identity(int,1,1)
,oid=a.id
,o金额=a.金额
,金额=cast(round(a.金额/(len(a.部门)-len(replace(a.部门,',',''))+1.0),2) as decimal(10,2))
,部门=substring(a.部门,b.id,charindex(',',a.部门+',',b.id)-b.id)
into #t1 from tb a,#t b
where substring(','+a.部门,b.id,1)=','
order by a.id,b.idselect a.id
,金额=case
when a.id=b.id then o金额-(select sum(金额) from #t1 where oid=a.oid and id<b.id)
else a.金额 end
,a.部门
from #t1 a
left join(
select id=max(id) from #t1 group by oid
)b on a.id=b.iddrop table #t,#t1
go--删除测试
drop table tb/*--测试结果id 金额 部门
----------- ------------ ---------------
1 333.33 IT
2 333.33 HR
3 333.34 FIN
4 1000.00 ADMIN
5 1000.00 FIN
6 1000.00 Sales
7 1000.00 Marketing
8 1000.00 Qulity(所影响的行数为 8 行)
--*/