--参照这个--示例数据 create table tb(ID int,Name varchar(10),ParentID int) insert tb select 1,'AAAA' ,0 union all select 2,'BBBB' ,0 union all select 3,'CCCC' ,0 union all select 4,'AAAA-1' ,1 union all select 5,'AAAA-2' ,1 union all select 6,'BBBB-1' ,2 union all select 7,'CCCC-1' ,3 union all select 8,'CCCC-2' ,3 union all select 9,'AAAA-1-1',4go declare @i int set @i=1 create table #tmp(id int,px varchar(1000),parentid int) insert into #tmp select [id], '', parentID from tb order by id update #tmp set px=cast(@i+10000000 as varchar) ,@i=@i+1 from #tmp where parentid=0 while exists (select 1 from #tmp a join #tmp b on a.id=b.parentID and a.px<>'' and b.px='')update b set b.px=a.px+cast(@i+10000000 as varchar),@i=@i+1 from #tmp a join #tmp b on a.id=b.parentID and a.px<>'' and b.px=''select b.*,space(len(a.px)/2)+b.name 缩进的 from #tmp a join tb b on a.id=b.id order by a.px drop table #tmp drop table tb所影响的行数为 1 行)ID Name ParentID 缩进的 ----------- ---------- ----------- ------------------------ 1 AAAA 0 AAAA 4 AAAA-1 1 AAAA-1 9 AAAA-1-1 4 AAAA-1-1 5 AAAA-2 1 AAAA-2 2 BBBB 0 BBBB 6 BBBB-1 2 BBBB-1 3 CCCC 0 CCCC 7 CCCC-1 3 CCCC-1 8 CCCC-2 3 CCCC-2(所影响的行数为 9 行)说明:1、树的深度不能超过5 2、
[Store_information_id] [bigint] IDENTITY (1, 1) NOT NULL ,
[Store_no] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ParentID] [bigint] NULL ,
[Store_sort] [int] NOT NULL ,
[Store_name] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Store_address] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[Store_tel] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[Store_owner] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[Store_owner_address] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[Store_owner_tel] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[Store_owner_email] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[Store_email] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[Store_type] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Store_fax] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[Store_account] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[Store_associate_time] [datetime] NULL ,
[Inputdate] [datetime] NULL ,
[Operator] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Available] [int] NULL
) ON [PRIMARY]
GO
else ParentID
end,Store_no
————
这样是不行的,我需要父商店后紧跟着他的子商店,你这样做是会错开的
else ParentID
end,Store_no
else ParentID
end,
case ParentID when -1 then 0
else 1
end,
Store_no
http://community.csdn.net/Expert/topic/3689/3689836.xml?temp=.7921411
create table tb(ID int,Name varchar(10),ParentID int)
insert tb select 1,'AAAA' ,0
union all select 2,'BBBB' ,0
union all select 3,'CCCC' ,0
union all select 4,'AAAA-1' ,1
union all select 5,'AAAA-2' ,1
union all select 6,'BBBB-1' ,2
union all select 7,'CCCC-1' ,3
union all select 8,'CCCC-2' ,3
union all select 9,'AAAA-1-1',4go
declare @i int
set @i=1
create table #tmp(id int,px varchar(1000),parentid int)
insert into #tmp select [id], '', parentID from tb order by id update #tmp set px=cast(@i+10000000 as varchar) ,@i=@i+1
from #tmp where parentid=0
while exists (select 1 from #tmp a join #tmp b
on a.id=b.parentID and a.px<>'' and b.px='')update b set b.px=a.px+cast(@i+10000000 as varchar),@i=@i+1
from #tmp a join #tmp b
on a.id=b.parentID and a.px<>'' and b.px=''select b.*,space(len(a.px)/2)+b.name 缩进的 from
#tmp a join tb b on a.id=b.id order by a.px drop table #tmp
drop table tb所影响的行数为 1 行)ID Name ParentID 缩进的
----------- ---------- ----------- ------------------------
1 AAAA 0 AAAA
4 AAAA-1 1 AAAA-1
9 AAAA-1-1 4 AAAA-1-1
5 AAAA-2 1 AAAA-2
2 BBBB 0 BBBB
6 BBBB-1 2 BBBB-1
3 CCCC 0 CCCC
7 CCCC-1 3 CCCC-1
8 CCCC-2 3 CCCC-2(所影响的行数为 9 行)说明:1、树的深度不能超过5 2、