select 产品编号,(select sum(数量) from table where left(产品编号,len(A.产品编号))=A.产品编号 and isnumeric(left(产品编号,len(A.产品编号)+1)=0 ) from table A where isnumeric(产品编号)=1
--测试:create table t1(产品编号 varchar(20),分类编号 int,数量 int) insert into t1 select '01abcd1','3','2' insert into t1 select '01abcd2','3','3' insert into t1 select '01abcd3','3','3' insert into t1 select '01','1',null insert into t1 select '02abcd1','3','4' insert into t1 select '02abcd2','3','5' insert into t1 select '02abcd3','3','6' insert into t1 select '02','1',null select b.产品编号,sum(IsNull(a.数量,0)) as 数量 from t1 a join (select 产品编号 from t1 where 分类编号=1) b on left(a.产品编号,len(b.产品编号)) = b.产品编号 group by b.产品编号/*产品编号 数量 -------------------- ----------- 01 8 02 15(所影响的行数为 2 行) */
create table t1(产品编号 varchar(20),分类编号 int,数量 int) insert into t1 select '01abcd1','3','2' insert into t1 select '01abcd2','3','3' insert into t1 select '01abcd3','3','3' insert into t1 select '01a','1',null insert into t1 select '02abcd1','3','4' insert into t1 select '02abcd2','3','5' insert into t1 select '02abcd3','3','6' insert into t1 select '02abcd','1',null select b.产品编号,sum(IsNull(a.数量,0)) as 数量 from t1 a join (select 产品编号 from t1 where 分类编号=1) b on left(a.产品编号,len(b.产品编号)) = b.产品编号 group by b.产品编号/*产品编号 数量 -------------------- ----------- 01a 8 02abcd 15(所影响的行数为 2 行)*/
select 产品编号,(select sum(数量) from 表 where 产品编号 like t2.产品编号+'%') from 表 t2 where 分类编号=1 相关子查询
insert into t1 select '01abcd1','3','2'
insert into t1 select '01abcd2','3','3'
insert into t1 select '01abcd3','3','3'
insert into t1 select '01','1',null
insert into t1 select '02abcd1','3','4'
insert into t1 select '02abcd2','3','5'
insert into t1 select '02abcd3','3','6'
insert into t1 select '02','1',null
select b.产品编号,sum(IsNull(a.数量,0)) as 数量
from t1 a
join (select 产品编号 from t1 where 分类编号=1) b on left(a.产品编号,len(b.产品编号)) = b.产品编号
group by b.产品编号/*产品编号 数量
-------------------- -----------
01 8
02 15(所影响的行数为 2 行)
*/
insert into t1 select '01abcd1','3','2'
insert into t1 select '01abcd2','3','3'
insert into t1 select '01abcd3','3','3'
insert into t1 select '01a','1',null
insert into t1 select '02abcd1','3','4'
insert into t1 select '02abcd2','3','5'
insert into t1 select '02abcd3','3','6'
insert into t1 select '02abcd','1',null
select b.产品编号,sum(IsNull(a.数量,0)) as 数量
from t1 a
join (select 产品编号 from t1 where 分类编号=1) b on left(a.产品编号,len(b.产品编号)) = b.产品编号
group by b.产品编号/*产品编号 数量
-------------------- -----------
01a 8
02abcd 15(所影响的行数为 2 行)*/
from 表 t2
where 分类编号=1 相关子查询