--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
Set Nocount On
declare @A table([elno] nvarchar(3))
Insert @A
select N'001' union all
select N'002' union all
select N'003'
Set Nocount On
declare @B table([elno] nvarchar(3),[bono] nvarchar(4))
Insert @B
select N'001',N'a10' union all
select N'001',N'a11' union all
select N'002',N'b21' union all
select N'003',N'b10' union all
select N'a10',N'a120' union all
select N'b21',N'b210'
;With t As
(
Select [elno],Convert(nvarchar(4000),'000')As LevelNo,Convert(nvarchar(4),'') As [bono],t=[elno] From @A
Union All
Select a.[elno],'000'+Right(Power(10,3)+Row_number() Over(Partition By a.[elno] Order by a.[bono]),3) As LevelNo,a.[bono],t=a.[elno]
From @B A Inner Join @A b On b.[elno]=a.[elno]
Union All
Select a.[elno],b.LevelNo+Right(Power(10,3)+Row_number() Over(Partition By b.t Order by a.[bono]),3) As LevelNo,[bono]=a.[bono],b.t
From @B a Inner Join t b On a.[elno]=b.[bono]
)
Select
A=Case [bono] When '' Then t Else '' End,
B=Isnull(Replicate(Char(32),len(levelNo)-4)+'--'+ [bono],'')
From t Order By t,levelno
/*
A B
---- -----------------
001
--a10
--a120
--a11
002
--b21
--b210
003
--b10
*/
将表转为以下结构层1,层2,层3...层n,名称
1 0 0 .... 001
1 1 0 ... a10
1 1 1 ... a120
1 2 0 ... a11
2 0 0 ... 002
2 1 0 ... b21
2 1 1 ... b210 再对以上临时表查询,生出结果.
insert into @t1
select '001' union
select '002' union
select '003' declare @t2 table (elno varchar(5),bono varchar(5))
insert into @t2
select '001', 'a10' union
select '001', 'a11' union
select '002', 'b21' union
select '003', 'b10' union
select 'a10', 'a120' union
select 'b21', 'b210'
;with s as
( select elno,cast(elno as varchar(100)) as path,0 as lv
from @t1
union all
select b.bono,cast(a.path+b.elno as varchar(100)) as path,a.lv+1 as lv
from s a
inner join @t2 b on a.elno=b.elno
)
select case lv when 0 then '' else space(lv)+'--' end + elno as elno
from s
order by path+elno/*
elno
---------
001
--a10
--a120
--a11
002
--b21
--b210
003
--b10(所影响的行数为 9 行)
*/
insert into @t1
select '001' union
select '002' union
select '003' declare @t2 table (elno varchar(5),bono varchar(5))
insert into @t2
select '001', 'a10' union
select '001', 'a11' union
select '002', 'b21' union
select '002', 'a10' union
select '003', 'b10' union
select 'a10', 'a120' union
select 'a10', 'b210' union
select 'b21', 'b210'
;with s as
( select elno,cast(elno as varchar(100)) as path,0 as lv
from @t1
union all
select b.bono,cast(a.path+b.elno as varchar(100)) as path,a.lv+1 as lv
from s a
inner join @t2 b on a.elno=b.elno
)
select case lv when 0 then '' else space(lv)+'--' end + elno as elno
from s
order by path+elno /*
001
--a10
--a120
--b210
--a11
002
--a10
--a120
--b210
--b21
--b210
003
--b10
*/
伺服器: 訊息 156,層級 15,狀態 1,行 19
關鍵字 'with' 附近的語法不正確。