--示例--测试数据
create table tb(ID nvarchar(20),DESCRIPTION nvarchar(20))
insert tb select '01' ,'电脑'
union all select '01.01' ,'CPU'
union all select '01.02' ,'主板'
union all select '01.01.0001','INTEL P4 2.8'
union all select '01.02.0001','华硕P4'
go--查询
select a.id,商品名称=a.DESCRIPTION
,第一级名称=b1.DESCRIPTION
,第二级名称=b2.DESCRIPTION
from tb a
left join tb b1 on len(b1.id)-len(replace(b1.id,'.',''))=0 and a.id like b1.id+'.%'
left join tb b2 on len(b2.id)-len(replace(b2.id,'.',''))=1 and a.id like b2.id+'.%'
where not exists(
select * from tb
where id like a.id+'.%')
go--删除测试
drop table tb/*--测试结果id 商品名称 第一级名称 第二级名称
-------------------- -------------------- -------------------- --------------------
01.01.0001 INTEL P4 2.8 电脑 CPU
01.02.0001 华硕P4 电脑 主板(所影响的行数为 2 行)
--*/
create table tb(ID nvarchar(20),DESCRIPTION nvarchar(20))
insert tb select '01' ,'电脑'
union all select '01.01' ,'CPU'
union all select '01.02' ,'主板'
union all select '01.01.0001','INTEL P4 2.8'
union all select '01.02.0001','华硕P4'
go--查询
select a.id,商品名称=a.DESCRIPTION
,第一级名称=b1.DESCRIPTION
,第二级名称=b2.DESCRIPTION
from tb a
left join tb b1 on len(b1.id)-len(replace(b1.id,'.',''))=0 and a.id like b1.id+'.%'
left join tb b2 on len(b2.id)-len(replace(b2.id,'.',''))=1 and a.id like b2.id+'.%'
where not exists(
select * from tb
where id like a.id+'.%')
go--删除测试
drop table tb/*--测试结果id 商品名称 第一级名称 第二级名称
-------------------- -------------------- -------------------- --------------------
01.01.0001 INTEL P4 2.8 电脑 CPU
01.02.0001 华硕P4 电脑 主板(所影响的行数为 2 行)
--*/
,第一级名称=b1.DESCRIPTION
,第二级名称=b2.DESCRIPTION
,第三级名称=b3.DESCRIPTION
from tb a
left join tb b1 on len(b1.id)-len(replace(b1.id,'.',''))=0 and a.id like b1.id+'.%'
left join tb b2 on len(b2.id)-len(replace(b2.id,'.',''))=1 and a.id like b2.id+'.%'
left join tb b3 on len(b3.id)-len(replace(b3.id,'.',''))=2 and a.id like b3.id+'.%'
where not exists(
select * from tb
where id like a.id+'.%')
declare @len int
declare @s varchar(1000)select top 1 @maxlevel=len(id)- replace(id,'.','') from 表
select top 1 @len=len(id) from 表
set rowcount @len
select idd=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0set @s='select id,DESCRIPTION as 商品名称 '
select @s=@s+',第'+substring('一二三四五六七八九十',
len(left(id,idd))-len(replace(left(id,idd),'.',''))+1
,1)+'级名称'
+'=case when idd<len(id) then (select top 1 DESCRIPTION
from 表 where id='''+left(a.id,idd)+''')
else ''
end'
from #t
set @s=' from 表 a where len(id)-replace(id,'.','')='+cast(@maxlevel as varchar)exec(@s)
declare @maxlevel int
declare @len int
declare @s varchar(1000)select top 1 @maxlevel=len(id)- replace(id,'.','') from 表
select top 1 @len=len(id) from 表
set rowcount @len
select idd=identity(int,1,1) into #t from sysobjects a,sysobjects b
set rowcount 0set @s='select id,DESCRIPTION as 商品名称 '
select @s=@s+',第'+substring('一二三四五六七八九十',
len(left(id,idd))-len(replace(left(id,idd),'.',''))+1
,1)+'级名称'
+'=case when idd<len(id) then (select top 1 DESCRIPTION
from 表
where id='''+left(a.id,idd)+''')
else ''''
end'
from #t
set @s=' from 表 a where len(id)-replace(id,'.','')='+cast(@maxlevel as varchar)exec(@s)
create table tb(ID nvarchar(20),DESCRIPTION nvarchar(20))
insert tb select '01' ,'电脑'
union all select '01.01' ,'CPU'
union all select '01.02' ,'主板'
union all select '01.01.0001','INTEL P4 2.8'
union all select '01.02.0001','华硕P4'
go--查询
select a.id,商品名称=a.DESCRIPTION
,第一级名称=b1.DESCRIPTION
,第二级名称=b2.DESCRIPTION
from tb a
left join tb b1 on len(b1.id)-len(replace(b1.id,'.',''))=0 and a.id like b1.id+'.%'
left join tb b2 on len(b2.id)-len(replace(b2.id,'.',''))=1 and a.id like b2.id+'.%'
where not exists(
select * from tb
where id like a.id+'.%')
go--删除测试
drop table tb/*--测试结果id 商品名称 第一级名称 第二级名称
-------------------- -------------------- -------------------- --------------------
01.01.0001 INTEL P4 2.8 电脑 CPU
01.02.0001 华硕P4 电脑 主板(所影响的行数为 2 行)