select t.分级内码 , (select sum(...) from a where 分级内码 like a.分级内码 + '%' and 分级内码 <> a.分级内码) from a t
select 分级内码, 汇总=(select sum(字段) from tb where charindex(a.分级内码,分级内码)>0) from tb a
create table A(分级内码 varchar(10),val int) insert into a values('01' ,1) insert into a values('0101' ,2) insert into a values('010101',3) insert into a values('0102' ,4) insert into a values('010201',5) go select t.分级内码 , (select sum(val) from a where 分级内码 like t.分级内码 + '%') from a t /* 分级内码 ---------- ----------- 01 15 0101 5 010101 3 0102 9 010201 5(所影响的行数为 5 行) */select t.分级内码 , (select sum(val) from a where 分级内码 like t.分级内码 + '%' and 分级内码 <> t.分级内码) from a t /* 分级内码 ---------- ----------- 01 14 0101 3 010101 NULL 0102 5 010201 NULL(所影响的行数为 5 行) */select t.分级内码 , isnull((select sum(val) from a where 分级内码 like t.分级内码 + '%' and 分级内码 <> t.分级内码),0) from a t /* 分级内码 ---------- ----------- 01 14 0101 3 010101 0 0102 5 010201 0(所影响的行数为 5 行) */select t.分级内码 , isnull((select sum(val) from a where 分级内码 like t.分级内码 + '%' and 分级内码 <> t.分级内码),val) from a t /* 分级内码 ---------- ----------- 01 14 0101 3 010101 3 0102 5 010201 5(所影响的行数为 5 行) */drop table a
(select sum(...) from a where 分级内码 like a.分级内码 + '%' and 分级内码 <> a.分级内码)
from a t
汇总=(select sum(字段) from tb where charindex(a.分级内码,分级内码)>0)
from tb a
insert into a values('01' ,1)
insert into a values('0101' ,2)
insert into a values('010101',3)
insert into a values('0102' ,4)
insert into a values('010201',5)
go
select t.分级内码 ,
(select sum(val) from a where 分级内码 like t.分级内码 + '%')
from a t
/*
分级内码
---------- -----------
01 15
0101 5
010101 3
0102 9
010201 5(所影响的行数为 5 行)
*/select t.分级内码 ,
(select sum(val) from a where 分级内码 like t.分级内码 + '%' and 分级内码 <> t.分级内码)
from a t
/*
分级内码
---------- -----------
01 14
0101 3
010101 NULL
0102 5
010201 NULL(所影响的行数为 5 行)
*/select t.分级内码 ,
isnull((select sum(val) from a where 分级内码 like t.分级内码 + '%' and 分级内码 <> t.分级内码),0)
from a t
/*
分级内码
---------- -----------
01 14
0101 3
010101 0
0102 5
010201 0(所影响的行数为 5 行)
*/select t.分级内码 ,
isnull((select sum(val) from a where 分级内码 like t.分级内码 + '%' and 分级内码 <> t.分级内码),val)
from a t
/*
分级内码
---------- -----------
01 14
0101 3
010101 3
0102 5
010201 5(所影响的行数为 5 行)
*/drop table a