漏了一个字段
id parentid mc score qz
206 0 a 0.3
207 0 b 0.4
208 0 c 80 0.4
209 206 a1 80 0.6
210 206 a2 90 0.4
211 207 b1 80 0.7
212 207 b2 75 0.3 转换为
id parentid mc score qz
206 0 a 84 0.3
209 206 a1 80 0.6
210 206 a2 90 0.4
207 0 b 78.5 0.4
211 207 b1 80 0.7
212 207 b2 75 0.3
208 0 c 80 0.3 再解释一下score根节点的算法
所有子节点的score乘qz
如:计算mc名称为a 80*0.6+90*0.4=84
计算mc名称为c 由于没有子节点则为 80
id parentid mc score qz
206 0 a 0.3
207 0 b 0.4
208 0 c 80 0.4
209 206 a1 80 0.6
210 206 a2 90 0.4
211 207 b1 80 0.7
212 207 b2 75 0.3 转换为
id parentid mc score qz
206 0 a 84 0.3
209 206 a1 80 0.6
210 206 a2 90 0.4
207 0 b 78.5 0.4
211 207 b1 80 0.7
212 207 b2 75 0.3
208 0 c 80 0.3 再解释一下score根节点的算法
所有子节点的score乘qz
如:计算mc名称为a 80*0.6+90*0.4=84
计算mc名称为c 由于没有子节点则为 80
alter proc sp_test
as
select * into #tmpTab from tab
declare @id varchar(10)
declare @str varchar(4000)
declare @sNo varchar(100)
declare curTest cursor for select id from tab
open curTest
fetch next from curTest into @id
while @@fetch_status=0
begin
set @sNo=ltrim(str(@id))
if exists(select parentid from tab where parentid=@id)
begin
set @str=' update #tmpTab set score=(select sum(isnull(score,0)*qz) from tab '+
' where parentid= '+ @sNo +
' group by parentid) where #tmpTab.id='+@sNo
print @str
end
else
begin
set @str=' update #tmpTab set score=(select isnull(score,0) '+
' from tab where id='+@sNo
print @str
end
exec(@str) fetch next from curTest into @id
end
close curTest
deallocate curTest
select * from #tmpTab
drop table #tmpTab
Go
if exists(select * from sysobjects where name='tab' and xtype='U')
drop table tab
Go
create table tab
(id int,
parentid int,
mc varchar(10),
score decimal(16,2),
qz decimal(16,2))insert into tab values(206,0,'a',null,0.3)
insert into tab values(207,0,'b',null,0.4)
insert into tab values(208,0,'c',80,0.4)
insert into tab values(209,206,'a1',80,0.6)
insert into tab values(210,206,'a2',90,0.4)
insert into tab values(211,207,'b1',80,0.7)
insert into tab values(212,207,'b2',75,0.3)--测试结果
----------------------------
id parentid mc score qz
-----------------------------
206 0 a 84.00 .30
207 0 b 78.50 .40
208 0 c 80.00 .40
209 206 a1 80.00 .60
210 206 a2 90.00 .40
211 207 b1 80.00 .70
212 207 b2 75.00 .30drop table tab
drop proc sp_test
select a.id,a.parentid,a.mc
,score=sum(case when b.id is null then a.score else b.score*b.qz end)
,a.qz
from 表 a
left join 表 b on a.id=b.parentid
where b.id is null or b.parentid<>0
group by a.id,a.parentid,a.mc,a.qz
order by isnull(max(b.parentid),case a.parentid when 0 then a.id else a.parentid end),a.parentid
create table 表(id int,parentid int,mc varchar(10),score int,qz decimal(6,1))
insert 表 select 206, 0,'a', 0,0.3
union all select 207, 0,'b', 0,0.4
union all select 208, 0,'c', 80,0.4
union all select 209,206,'a1',80,0.6
union all select 210,206,'a2',90,0.4
union all select 211,207,'b1',80,0.7
union all select 212,207,'b2',75,0.3
go--查询
select a.id,a.parentid,a.mc
,score=sum(case when b.id is null then a.score else b.score*b.qz end)
,a.qz
from 表 a
left join 表 b on a.id=b.parentid
where b.id is null or b.parentid<>0
group by a.id,a.parentid,a.mc,a.qz
order by isnull(max(b.parentid),case a.parentid when 0 then a.id else a.parentid end),a.parentid
go--删除测试
drop table 表/*--测试结果id parentid mc score qz
----------- ----------- ---------- ---------- ------
206 0 a 84.0 .3
209 206 a1 80.0 .6
210 206 a2 90.0 .4
207 0 b 78.5 .4
211 207 b1 80.0 .7
212 207 b2 75.0 .3
208 0 c 80.0 .4(所影响的行数为 7 行)
--*/