请教SQL语句

[英文字母打字员]

数据表结构：

      ID      FID    name
       1       0      根节点
       2       1      二级根节点
       3       2      三级根节点
       4       3      四级根节点
       5       4      五级根节点

现在知道五级根节点的ID 需要查出的信息如下：根节点/二级根节点/三级根节点/四级根节点/五级根节点

哪位大侠知道，需要这样子的一个函数，谢谢

[Mr_Nice]

递归.... LZ 搜一下，坛子里很多。

[英文字母打字员]

[Quote=引用 1 楼 orchidcat 的回复:]
递归.... LZ 搜一下，坛子里很多。
[/Quote]
没看到什么好东西

[Felixzhaowenzhong]

--表结构
CREATE TABLE [dbo].[PSY_Web_MenuClass](
[ModuleCode] [varchar](32) NOT NULL,
[ModuleName] [varchar](128) NOT NULL,
[ShortModuleName] [varchar](64) NULL,
[LevelID] [int] NOT NULL,
[ParentCode] [varchar](32) NULL
CONSTRAINT [PK_PSY_WEB_MENUCLASS] PRIMARY KEY NONCLUSTERED
(
[ModuleCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


/* ModuleCode 根节点，ParentCode 父节点*/

CREATE FUNCTION f_MenuSort(@ModuleCode varchar(32)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ModuleCode varchar(32),sort int)
AS
BEGIN
DECLARE cod_cur CURSOR LOCAL
FOR
SELECT ModuleCode FROM PSY_Web_MenuClass
WHERE ParentCode=@ModuleCode OR (@ModuleCode IS NULL AND ParentCode IS NULL)
OPEN cod_cur
FETCH cod_cur INTO @ModuleCode
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ModuleCode,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归(嵌套查询)层数未超过32层(递归(嵌套查询)最大允许32层)
BEGIN
--开始递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_MenuSort(@ModuleCode,@sort)
SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数
END
FETCH cod_cur INTO @ModuleCode
END
RETURN
END
GO

SELECT a.*
FROM PSY_Web_MenuClass a,f_MenuSort('MYSUN',2) b
WHERE a.ModuleCode=b.ModuleCode


--------------------------------------------------2
CREATE FUNCTION f_LPad
(
@i int,@len int
)
RETURNS nvarchar(max)
AS
BEGIN
RETURN cast (replicate('0', @len - len(@i) ) + convert(nvarchar,@i) as nvarchar(max))
END



CREATE PROCEDURE PSYP_GetMenuTree
----单个父节点
@MenuCode varchar(32)=NULL
AS
BEGIN
with ReplicateMenu
as
(
select *,0 as Level,cast('0' as nvarchar(max)) as TreePath from PSY_Web_MenuClass where ModuleCode = @MenuCode
union all
select A.*,rm.Level + 1,rm.TreePath + dbo.f_LPad(Row_Number() over (order by A.ModuleCode desc),8) as TreePath from PSY_Web_MenuClass as A inner join ReplicateMenu as rm on A.ParentCode = rm.ModuleCode )
SELECT * from ReplicateMenu
END

----单个父节点


Create PROCEDURE PSYP_GetMenuTree
---多父节点
@MenuCode varchar(32)=NULL
AS
BEGIN
with ReplicateMenu
as
(
select *,0 as Level,cast(Row_Number() over (order by A.ModuleCode desc) as nvarchar(max)) as TreePath from PSY_Web_MenuClass where ModuleCode = @MenuCode
union all
select csc.*,rm.Level + 1,rm.TreePath + dbo.Lpad(Row_Number() over (order by A.ModuleCode desc),8) as TreePath from PSY_Web_MenuClass as A inner join ReplicateMenu as rm on A.ParentCode = rm.ModuleCode )
SELECT * from ReplicateMenu
END
---多父节点

[英文字母打字员]

[Quote=引用 3 楼 zhaowenzhong 的回复:]
--表结构
CREATE TABLE [dbo].[PSY_Web_MenuClass](
[ModuleCode] [varchar](32) NOT NULL,
[ModuleName] [varchar](128) NOT NULL,
[ShortModuleName] [varchar](64) NULL,
[LevelID] [int] NOT NULL,
[Par……
[/Quote]
看到眼睛大了

[Mr_Nice]

if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (ID int,FID int,name nvarchar(10))
insert into [TB]
select 1,0,'根节点' union all
select 2,1,'二级根节点' union all
select 3,2,'三级根节点' union all
select 4,3,'四级根节点' union all
select 5,4,'五级根节点'

select * from [TB]


;WITH CTE
AS(
SELECT CONVERT(VARCHAR(100),NAME) AS name,Fid,ID FROM TB WHERE FId = 0
UNION ALL
SELECT CONVERT(VARCHAR(100),CTE.NAME+'/'+TB.NAME) AS name,TB.FID,TB.ID FROM CTE INNER JOIN TB ON  CTE.ID = TB.fid
)

SELECT TOP 1 name FROM CTE ORDER BY fid DESC

/*
根节点/二级根节点/三级根节点/四级根节点/五级根节点*/

[-晴天]

[Quote=引用楼主 wanglejun 的回复:]
数据表结构：
SQL code


ID FID name
1 0 根节点
2 1 二级根节点
3 2 三级根节点
4 3 四级根节点
5 4 五级根节点


现在知道五级根节点的ID 需要查出的信息如下：根节点/二级根节点/三级根节点/四级根节点/五级根节点

[/Quote]

create table tb(ID    int,  FID int,   name nvarchar(10))
insert into tb select   1,0,'根节点'
insert into tb select    2,1,'二级根节点'
insert into tb select    3,2,'三级根节点'
insert into tb select     4,3,'四级根节点'
insert into tb select     5,4,'五级根节点'
go
;with cte as(
select *,id as flg,CONVERT(nvarchar(50),name)n from tb where FID=0
union all
select b.*,a.flg,CONVERT(nvarchar(50),a.n+'/'+b.name) from cte a inner join tb b on a.ID=b.FID
)select n from cte a where not exists(select 1 from cte where flg=a.flg and n>a.n)
/*
n
--------------------------------------------------
根节点/二级根节点/三级根节点/四级根节点/五级根节点

(1 行受影响)

*/
go
drop table tb

[--小F--]

--参考一下实例
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
 DROP TABLE [tb]
GO
CREATE TABLE [tb](GUID INT IDENTITY,[col1] NVARCHAR(10),[col2] NVARCHAR(20))
INSERT [tb]
SELECT N'A','01' UNION ALL
SELECT N'B','01.01' UNION ALL
SELECT N'C','01.01.01' UNION ALL
SELECT N'F','01.01.01.01' UNION ALL
SELECT N'E','01.01.01.02' UNION ALL
SELECT N'D','01.01.01.03' UNION ALL
SELECT N'O','02' UNION ALL
SELECT N'P','02.01' UNION ALL
SELECT N'Q','02.01.01' 
GO
--SELECT * FROM [tb]

-->SQL查询如下:

---另一种方法
;WITH T AS
(
   SELECT *,PATH=CAST([COL1] AS VARCHAR(1000)) FROM TB A
       WHERE NOT EXISTS(
        SELECT 1 FROM TB 
     WHERE A.COL2 LIKE COL2+'%' 
   AND LEN(A.COL2)>LEN(COL2))
   UNION ALL
   SELECT A.*,CAST(PATH+'-->'+A.COL1 AS VARCHAR(1000))
   FROM TB A 
   JOIN T B 
        ON A.COL2 LIKE B.COL2+'%' 

           AND LEN(A.COL2)-3=LEN(B.COL2)
)

SELECT * FROM T ORDER BY LEFT(COL2,2)

/*

GUID        COL1        COL2                  PATH

----------- ---------- -------------------- --------------------

1           A          01                   A

2           B          01.01                A-->B

3           C          01.01.01             A-->B-->C

4           F          01.01.01.01          A-->B-->C-->F

5           E          01.01.01.02          A-->B-->C-->E

6           D          01.01.01.03          A-->B-->C-->D

7           O          02                   O

8           P          02.01                O-->P

9           Q          02.01.01             O-->P-->Q
(9 行受影响)

*/


;WITH T AS

(
    SELECT *,CAST(COL1  AS VARCHAR(1000)) AS PATH
    FROM  TB 
    WHERE COL2 NOT LIKE '%.%'
    UNION ALL
    SELECT A.*,CAST(B.PATH+'-->'+A.COL1 AS VARCHAR(1000))
    FROM TB A,T B
    WHERE A.COL2 LIKE B.COL2+'.[01-99][01-99]'
)

SELECT * FROM T 
ORDER BY LEFT(COL2,2)

/*

GUID        COL1        COL2                  PATH

----------- ---------- -------------------- --------------------

1           A          01                   A

2           B          01.01                A-->B

3           C          01.01.01             A-->B-->C

4           F          01.01.01.01          A-->B-->C-->F

5           E          01.01.01.02          A-->B-->C-->E

6           D          01.01.01.03          A-->B-->C-->D

7           O          02                   O

8           P          02.01                O-->P

9           Q          02.01.01             O-->P-->Q

 (9 行受影响)

*/

[英文字母打字员]

[Quote=引用 5 楼 orchidcat 的回复:]
SQL code
if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (ID int,FID int,name nvarchar(10))
insert into [TB]
select 1,0,'根节点' union all
select 2,1,'二级根节点' union all
select……
[/Quote]
语句被终止。完成执行语句前已用完最大递归 100。