34,597
社区成员
发帖
与我相关
我的任务
分享求一个SQLServer2005的递归SQL语句，谢谢了
WITH RecursiveCategories (ParentID,ID ,CateName ,Parenturl,Level)
AS
(
SELECT 系统__路径ID, 路径ID, 路径名称,url, 0 AS Level
FROM 系统_导航路径
WHERE 系统__路径ID = 0
UNION ALL
SELECT 系统_导航路径.系统__路径ID, 系统_导航路径.路径ID, 系统_导航路径.路径名称,
系统_导航路径.url, Level + 1
FROM 系统_导航路径
INNER JOIN RecursiveCategories AS d
ON 系统_导航路径.系统__路径ID = d.ID
)
Select * From RecursiveCategories Order By Level Asc
上面是网上找的SQLServer2005提供的递归查询，但我看不懂啊，试了半天还是不行，哪位能帮忙写一个SQL，谢谢了
我的表结构：
code name paentCode
001 aaa 0
002 bbb 001
003 ccc 001
004 ddd 002
这个想要的结果应该是：
aaa
bbb
ddd
ccc
谢谢了～～
...全文
请发表友善的回复…
发表回复
zhl1015 2008-11-21
- 打赏
- 举报
呵呵，高手，谢谢各位了~~
CN_SQL 2008-11-19
- 打赏
- 举报
我的方法不正确，请忽略~
xjtlf 2008-11-19
- 打赏
- 举报
帮顶
android2008 2008-11-19
- 打赏
- 举报
帮顶
水族杰纶 2008-11-19
- 打赏
- 举报
CREATE TABLE tb(ID INT,PID int,Name nvarchar(10))
INSERT tb SELECT 1,0,N'部门1'
UNION ALL SELECT 2,0,N'部门2'
UNION ALL SELECT 3,1,N'部门1.1'
UNION ALL SELECT 4,3,N'部门1.1.1'
UNION ALL SELECT 5,4,N'部门1.1.1.1'
UNION ALL SELECT 6,2,N'部门2.1'
UNION ALL SELECT 7,6,N'部门2.1.1'
UNION ALL SELECT 8,7,N'部门2.1.1.1'
GO
--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID INT)
RETURNS @t_Level TABLE(ID INT,Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO
--调用函数查询节点[ 1 ]及其所有子节点
SELECT a.*
FROM tb a,f_Cid(1) b
WHERE a.ID=b.ID
/*--结果
ID PID Name
----------- ----------- ----------
1 0 部门1
3 1 部门1.1
4 3 部门1.1.1
5 4 部门1.1.1.1
(4 row(s) affected)
--*/
DROP FUNCTION F_Cid
DROP TABLE TB
-晴天 2008-11-19
- 打赏
- 举报
create table tb(code char(3),name char(3),parentcode char(3))
insert into tb select '001','aaa','0'
union all select '002','bbb','001'
union all select '003','ccc','001'
union all select '004','ddd','002'
go
with cte as(
select code,name,parentcode,convert(varchar,'') as flg,convert(varchar,code) as tmp from tb where parentcode='0'
union all
select sub.code,sub.name,sub.parentcode,convert(varchar,super.flg+' ') as flg,convert(varchar,super.tmp+sub.code) as tmp from tb sub,cte super
where sub.parentcode=super.code
)
select flg+name as name from cte order by tmp
go
drop table tb
/*
name
---------------------------------
aaa
bbb
ddd
ccc
(4 行受影响)
*/
等不到来世 2008-11-19
- 打赏
- 举报
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([code] varchar(3),[name] varchar(3),[parentcode] varchar(3))
insert [tb]
select '001','aaa','0' union all
select '002','bbb','001' union all
select '003','ccc','001' union all
select '004','ddd','002'
select * from [tb]
with cte as
(
select code,name=name,parentcode,sort=cast(code as varchar(8000)),lvl=0 from tb where parentcode='0'
union all
select a.code,a.name,a.parentcode,sort=c.sort+a.code,lvl=c.lvl+1 from tb a join cte c on a.parentcode=c.code
)
select space(lvl)+name from cte order by sort
--测试结果:
/*
----------------
aaa
bbb
ddd
ccc
(4 行受影响)
*/
CN_SQL 2008-11-19
- 打赏
- 举报
declare @t table
(
code char(3),
name char(3),
parentcode char(3)
)
insert @t select '001','aaa','0'
union all select '002','bbb','001'
union all select '003','ccc','001'
union all select '004','ddd','002'
--union all select '005','eee','004'
;with cte(code,name,parentcode,level) as
(
select code,name,parentcode,0 as level
from @t
where parentcode = 0
union all
select a.code,a.name,a.parentcode,level + 1
from @t a
join cte b
on b.code = a.parentcode
)
,cte2 as(
select
a.code,
a.name,
a.parentcode,
a.level,
codeB = isnull(b.code,0)
from cte a
left join @t b
on b.parentcode = a.code
)
,cte3 as(
select name,0 as level,0 as flag from cte2 where codeB > 0
union
select name,level,1 as flag from cte2 where codeB = 0
)
select
name
from cte3
order by flag,level desc
/**
aaa
bbb
ddd
ccc
**/
你可以再多增加一个子节点去验证
