T-SQL 递归函数 func

[lifenglijun]

例如我有一个表 Tree 结构如下
ID Text PID
1 A null
2 B 1
3 C 2
4 D 3

当我传进去 ID=4的时候，
得到 A->B->C->D

::》 SELECT GetDpt(PID,'') AS PP FROM Tree WHERE ID = '4'
::》 A->B->C->D
下面是我写的一个函数，不过有问题，请问各位前辈如何给解：：：：：.........多谢！！
MS SQL 2008

CREATE FUNCTION GetDPT(@dptId NVARCHAR(20),@dptname NVARCHAR(200))
RETURNS NVARCHAR(200)
AS
Begin
	declare @dptn NVARCHAR(50),@dpti NVARCHAR(20)
	SELECT @dptn=Text,@dpti=PID From Tree
	if(@dpti IS NULL OR @dpti = '')
		Begin
			Return @dptn;
		End
	Else
		Begin
			Return GetDPT(@dpti,@dptn)+' -> '+@dptn;
		End
End

[中国风]

自遞歸參照
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx

[槑党灬穆奇奇]

create table tb(ID int,[Text] varchar(1),PID int)
insert into tb
select 1,'A',null union all
select 2,'B',1 union all
select 3,'C',2 union all
select 4,'D',3

declare @sql varchar(30)
;with m7777 as(
select * from tb where ID=4
union all
select t.* from tb t,m7777 m where t.id=m.pid)
select @sql=isnull(@sql+'->','')+[Text] from m7777 order by [text]
select @sql
/*
A->B->C->D
*/

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tree') is null
	drop table Tree
Go
Create table Tree([ID] int,[Text] nvarchar(20),[PID] int)
Insert Tree
select 1,N'A',null union all
select 2,N'B',1 union all
select 3,N'C',2 union all
select 4,N'D',3
Go



create function GetDPT( @ID int)
returns varchar (100)
as

begin
declare @PID INT,@s NVARCHAR(20)
select @PID = [PID],@s=[Text] from Tree where ID = @ID
return case when @PID is null then @s
          else   isnull (dbo. GetDPT(@PID)+ '->' , '' )+ @s end

end
GO
SELECT dbo.GetDPT(ID) AS PP FROM Tree WHERE ID=4
/*
A->B->C->D
*/

[中国风]

以上函數可用在32層遞歸之內

如果是SQL2005以上版本可用2樓方法CTE

[lifenglijun]

[Quote=引用 2 楼 m7777 的回复:]
SQL code

create table tb(ID int,[Text] varchar(1),PID int)
insert into tb
select 1,'A',null union all
select 2,'B',1 union all
select 3,'C',2 union all
select 4,'D',3

declare @sql varchar(3……
[/Quote]

THANK 感谢

[lifenglijun]

一个一半，哈哈。多谢各位，费心了。

[htl258_Tony]

----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date   : 2011-05-19 17:25:59
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog   : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [Tree]
IF OBJECT_ID('[Tree]') IS NOT NULL
	DROP TABLE [Tree]
GO
CREATE TABLE [Tree] ([ID] [int],[Text] [nvarchar](10),[PID] [int])
INSERT INTO [Tree]
SELECT '1','A',NULL UNION ALL
SELECT '2','B','1' UNION ALL
SELECT '3','C','2' UNION ALL
SELECT '4','D','3'

--SELECT * FROM [Tree]
GO
-->SQL查询如下:

IF OBJECT_ID('getdpt')>0
	DROP FUNCTION getdpt
GO
CREATE FUNCTION GetDPT(@Id INT)
RETURNS NVARCHAR(200)
AS
BEGIN
	DECLARE @dptn NVARCHAR(50), @dpti NVARCHAR(20)
	SELECT @dptn = [Text], @dpti = PID 
	FROM Tree
	WHERE ID = @Id
	RETURN 
	CASE 
	     WHEN ISNULL(@dpti, '')='' THEN @dptn
	     ELSE ISNULL(dbo.GetDPT(@dpti)+'->', '')+@dptn
	END
END 
GO

SELECT dbo.GetDPT(4)
/*
A->B->C->D

(1 行受影响)
*/