create table #t(number varchar(100),name varchar(20),interid int,childNumber varchar(100),childName varchar(20))
insert into #t select rtrim('1.1 '),rtrim('a '),1,rtrim('1.1.1 '),rtrim('a1 ')
insert into #t select rtrim('1.1 '),rtrim('a '),2,rtrim('1.1.2 '),rtrim('a2 ')
insert into #t select rtrim('1.1 '),rtrim('a '),3,rtrim('1.1.3 '),rtrim('a3 ')
insert into #t select rtrim('1.2 '),rtrim('b '),1,rtrim('1.2.1 '),rtrim('b1 ')
insert into #t select rtrim('1.2 '),rtrim('b '),2,rtrim('1.2.2 '),rtrim('b2 ')
insert into #t select rtrim('1.1.2 '),rtrim('a2 '),1,rtrim('1.1.2.1 '),rtrim('a2-1 ')
insert into #t select rtrim('1.1.2 '),rtrim('a2 '),2,rtrim('1.1.2.2 '),rtrim('a2-2 ')
insert into #t select rtrim('1.1.2.1'),rtrim('a2-1'),1,rtrim('1.1.2.2.1'),rtrim('a2-1-1')
--执行查询
declare @var varchar(20)
set @var = '1.1'
select
层次 = REPLICATE('.',a.层次)+rtrim(a.层次),
a.代码,
a.名称
from
(select
层次 = (len(childNumber)-len(replace(childNumber,'.','')))-(len(@var)-len(replace(@var,'.',''))),
代码 = childNumber,
名称 = childName
from
#t
where
number like @var+'%') a
order by
层次 desc
--输出结果
层次 代码 名称
------- ---------- -------
.1 1.1.1 a1
.1 1.1.3 a3
.1 1.1.2 a2
..2 1.1.2.2 a2-2
..2 1.1.2.1 a2-1
...3 1.1.2.2.1 a2-1-1
insert into #t select rtrim('1.1 '),rtrim('a '),1,rtrim('1.1.1 '),rtrim('a1 ')
insert into #t select rtrim('1.1 '),rtrim('a '),2,rtrim('1.1.2 '),rtrim('a2 ')
insert into #t select rtrim('1.1 '),rtrim('a '),3,rtrim('1.1.3 '),rtrim('a3 ')
insert into #t select rtrim('1.2 '),rtrim('b '),1,rtrim('1.2.1 '),rtrim('b1 ')
insert into #t select rtrim('1.2 '),rtrim('b '),2,rtrim('1.2.2 '),rtrim('b2 ')
insert into #t select rtrim('1.1.2 '),rtrim('a2 '),1,rtrim('1.1.2.1 '),rtrim('a2-1 ')
insert into #t select rtrim('1.1.2 '),rtrim('a2 '),2,rtrim('1.1.2.2 '),rtrim('a2-2 ')
insert into #t select rtrim('1.1.2.1'),rtrim('a2-1'),1,rtrim('1.1.2.2.1'),rtrim('a2-1-1')
--执行查询
declare @var varchar(20)
set @var = '1.1'
select
层次 = REPLICATE('.',a.层次)+rtrim(a.层次),
a.代码,
a.名称
from
(select
层次 = (len(childNumber)-len(replace(childNumber,'.','')))-(len(@var)-len(replace(@var,'.',''))),
代码 = childNumber,
名称 = childName
from
#t
where
number like @var+'%') a
order by
层次 desc
--输出结果
层次 代码 名称
------- ---------- -------
.1 1.1.1 a1
.1 1.1.3 a3
.1 1.1.2 a2
..2 1.1.2.2 a2-2
..2 1.1.2.1 a2-1
...3 1.1.2.2.1 a2-1-1
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货