sql 树形 SQLGo

参照上述文章:
我设计了一个表：

引用

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST]
GO

CREATE TABLE [dbo].[TEST] (
[TEST_ID] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[NAME] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[PARENT] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

然后写了一个函数：

CREATE FUNCTION dbo.GetSubtreeInfo2

( @manager_id AS char(5) )

RETURNS @treeinfo table

( [TEST_ID] [char] (10) NOT NULL,

[级别] [int] NOT NULL

)  AS

BEGIN

DECLARE @level AS int

SELECT @level = 0

INSERT INTO @treeinfo

SELECT [TEST_ID], @level

FROM [TEST]

WHERE [TEST_ID] = @manager_id

WHILE @@ROWCOUNT > 0

BEGIN

SET @level = @level + 1

INSERT INTO @treeinfo

SELECT E.[TEST_ID], @level

FROM [TEST] AS E JOIN @treeinfo AS T

ON E.[PARENT] = T.[TEST_ID] AND T.[级别] = @level - 1

END

RETURN

END



在查询分析器里执行：

引用

SELECT *
FROM GetSubtreeInfo2('12')

得到节点12及其子结点的ID。