sql2005----cte----递归结构(递归、层级、全路径)

--1、创建表结构
CREATE TABLE [tb_loc](
    [id] [int],
    [name] [varchar](16),
    [parent] [int]
)
GO
--2、创建测试数据
INSERT tb_loc(id,name,parent) VALUES( 1,'河北省',NULL)
INSERT tb_loc(id,name,parent) VALUES( 2,'石家庄',1)
INSERT tb_loc(id,name,parent) VALUES( 3,'保定',1)
INSERT tb_loc(id,name,parent) VALUES( 4,'山西省',NULL)
INSERT tb_loc(id,name,parent) VALUES( 5,'太原',4)
INSERT tb_loc(id,name,parent) VALUES( 6,'新华区',2)
INSERT tb_loc(id,name,parent) VALUES( 7,'北焦村',6)
INSERT tb_loc(id,name,parent) VALUES( 8,'大郭村',6)
INSERT tb_loc(id,name,parent) VALUES( 9,'河南省',NULL)
INSERT tb_loc(id,name,parent) VALUES( 10,'大郭村南',8)
INSERT tb_loc(id,name,parent) VALUES( 11,'大郭村北',8)
INSERT tb_loc(id,name,parent) VALUES( 12,'北焦村东',7)
INSERT tb_loc(id,name,parent) VALUES( 13,'北焦村西',7)
INSERT tb_loc(id,name,parent) VALUES( 14,'桥东区',3)
INSERT tb_loc(id,name,parent) VALUES( 15,'桥西区',3)
GO
--3、递归结构
WITH locs(id,name,parent,loclevel,sortseq)AS(
    SELECT id,name,parent,1 AS loclevel,('.'+cast(id as varchar(MAX))) as sortseq
   FROM tb_loc WHERE parent IS NULL or len(parent)<=0
    UNION ALL
    SELECT l.id,l.name,l.parent,loclevel+1,cast((sortseq+'.'+cast(l.id as VARCHAR(MAX))) as VARCHAR(MAX))
   FROM tb_loc l INNER JOIN locs p ON l.parent=p.id
)
SELECT id,name,parent,loclevel,Replicate('　',loclevel)+'┠'+name nameUnion,sortseq FROM locs order by sortseq
--4、删除表
drop table tb_loc