if exists(select 1 from sysobjects where id=OBJECT_ID('t_maxLevel') and xtype='u')drop table t_maxLevelgocreate table t_maxLevel(id int identity(1,1) not null primary key,pid int not null,levels int not null,className varchar(50) null default '',orderNo int null default 0,updateDate datetime null default getdate())insert into t_maxLevel values(0,1,'集团董事会',1,getdate())insert into t_maxLevel values(1,2,'总经办',1,getdate())insert into t_maxLevel values(1,2,'行政部',2,getdate())insert into t_maxLevel values(1,2,'人力资源部',3,getdate())insert into t_maxLevel values(1,2,'市场部',4,getdate())insert into t_maxLevel values(1,2,'企划部',5,getdate())insert into t_maxLevel values(1,2,'IT信息部',6,getdate())insert into t_maxLevel values(1,2,'财务部',7,getdate())insert into t_maxLevel values(1,2,'后勤部',8,getdate())insert into t_maxLevel values(2,3,'董事长',1,getdate())insert into t_maxLevel values(2,3,'董事会秘书',2,getdate())insert into t_maxLevel values(10,4,'董事长秘书',1,getdate())insert into t_maxLevel values(10,4,'总经理',2,getdate())insert into t_maxLevel values(13,5,'总助',1,getdate())GO;WITH Ta AS(SELECT *,CAST(RIGHT(10000+id,4) AS VARCHAR(8000)) AS Ord FROM t_maxLevel WHERE pid=0UNION ALLSELECT a.* ,b.Ord+CAST(RIGHT(10000+a.id,4) AS VARCHAR(8000)) AS OrdFROM t_maxLevel AS aINNER JOIN TA AS b ON b.ID=a.pid)SELECT REPLICATE('--',levels)+className AS className ,Ord AS 排序显示 FROM Ta ORDER BY ord/*className排序显示---------------------------------------------集团董事会0001----总经办00010002------董事长000100020010--------董事长秘书0001000200100012--------总经理0001000200100013----------总助00010002001000130014------董事会秘书000100020011----行政部00010003----人力资源部00010004----市场部00010005----企划部00010006----IT信息部00010007----财务部00010008----后勤部00010009*/
