我现在的数据库结构是 这样的
ID PARID
1 0
2 0
3 0
4 1
5 4
6 3
7 6想利用SQL05的递归查询 直接按
1
4
5
3
6
7这样的顺序查出来绑定到treeview中,我该怎么处理?先麻烦大家了 ,我对数据库的处理不是很在行
ID PARID
1 0
2 0
3 0
4 1
5 4
6 3
7 6想利用SQL05的递归查询 直接按
1
4
5
3
6
7这样的顺序查出来绑定到treeview中,我该怎么处理?先麻烦大家了 ,我对数据库的处理不是很在行
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-07 11:29:30
---------------------------------
--> 生成测试数据表-tbif not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[PARID] int)
Insert tb
select 1,0 union all
select 2,0 union all
select 3,0 union all
select 4,1 union all
select 5,4 union all
select 6,3 union all
select 7,6
Go
--Select * from tb-->SQL查询如下:
;with t as
(
select *,id px from tb where parid=0
union all
select tb.*,t.px from tb,t where tb.parid=t.id
)
select ID,PARID from t order by px
/*
ID PARID
----------- -----------
1 0
4 1
5 4
2 0
3 0
6 3
7 6(7 行受影响)
*/
ID PARID
1 0
2 0
3 0
4 1
5 4
6 3
7 6
8 4
9 1
10 9
查的结果是1 0
4 1
5 4
8 4
9 1
10 92 03 0
6 3
7 6
也就是说 我可能存在N层的子节点 都按照树状结构显示出来 麻烦大家了
AS
(
SELECT ID,PARID,0,ID FROM CMS_Site_Class WHERE PARID = 0
UNION ALL
SELECT A.ID,A.PARID,B.LEVELS+1,B.TOPLEVEL FROM CMS_Site_Class A
INNER JOIN
TREE B ON A.PARID = B.ID
)
SELECT * FROM TREE ORDER BY TOPLEVEL,LEVELS
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-07 15:37:42
---------------------------------
--> 生成测试数据表-tbif not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[PARID] int)
Insert tb
select 1,0 union all
select 2,0 union all
select 3,0 union all
select 4,1 union all
select 5,4 union all
select 6,3 union all
select 7,6 union all
select 8,4 union all
select 9,1 union all
select 10,9
Go
--Select * from tb-->SQL查询如下:
;with t as
(
select *,id px,cast(id as nvarchar(max)) px2 from tb where parid=0
union all
select tb.*,t.px,t.px2+ltrim(tb.id) from tb,t where tb.parid=t.id
)
select ID,PARID from t order by px,px2/*
ID PARID
----------- -----------
1 0
4 1
5 4
8 4
9 1
10 9
2 0
3 0
6 3
7 6(10 行受影响)
*/