create table #TA(code varchar(100),name varchar(100),value int)
insert #ta values('001','张三/李四', 10)
insert #ta values('002','王五', 6)
insert #ta values('003','赵六/王二/刘三', 6)
select top 8000 identity(int,1,1) id into #dali from sysobjects a,sysobjects bselect code,substring('/'+name,id+1,charindex('/','/'+name+'/',id+1)-id-1),value/(len(name)-len(replace(name,'/',''))+1)
from #TA aa,#dali bb
where substring('/'+name,id,8000) like '/_%'
order by code,idgo
drop table #ta,#dali
insert #ta values('001','张三/李四', 10)
insert #ta values('002','王五', 6)
insert #ta values('003','赵六/王二/刘三', 6)
select top 8000 identity(int,1,1) id into #dali from sysobjects a,sysobjects bselect code,substring('/'+name,id+1,charindex('/','/'+name+'/',id+1)-id-1),value/(len(name)-len(replace(name,'/',''))+1)
from #TA aa,#dali bb
where substring('/'+name,id,8000) like '/_%'
order by code,idgo
drop table #ta,#dali
select top 8000 identity(int,1,1) id into #dali from sysobjects a,sysobjects b
的意思吗??
select name, sum(score) from (
select
substring('/'+name,id+1,charindex('/','/'+name+'/',id+1)-id-1) name,value/(len(name)-len(replace(name,'/',''))+1) score
from #TA aa,#dali bb
where substring('/'+name,id,8000) like '/_%') A
group by name
(小道消息:大家都开始怀疑,大力的血统是否纯正的地球人...)