在与同事讨论中得到一个思路不用嵌套调用直接用一个语句就可以生成树实事上他已经做好了该程序他用了临时表写了一个存储过程我改写为一个表值函数供大家参考
表结构及表值函数如下
查询树表语句
/**//**** Object: Table [dbo][Tree] Script Date: :: ***/
CREATE TABLE [dbo][Tree] (
[ID] [int] IDENTITY ( ) NOT NULL
[PID] [int] NULL
[Name] [varchar] () COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_Tree] ON [dbo][Tree]([PID]) ON [PRIMARY]
GO
ALTER TABLE [dbo][Tree] WITH NOCHECK ADD
CONSTRAINT [PK_Tree] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
CONSTRAINT [子ID不能等于父ID] CHECK ([ID] <> [PID])
GO
ALTER TABLE [dbo][Tree] ADD
CONSTRAINT [FK_Tree_Tree] FOREIGN KEY
(
[PID]
) REFERENCES [dbo][Tree] (
[ID]
)
GO
/**//****** 对象: 用户定义的函数 dbofGetTreeTable
脚本日期: :: ******/
CREATE FUNCTION dbofGetTreeTable
(
@ID int= null
)
RETURNS @Tab TABLE(ID int PID int Name varchar() Lev int)
AS
BEGIN
Declare @lev int
Set @lev=
While @lev= or @@ROWCount>
Begin
Set @Lev=@Lev+
Insert @Tab(ID PID Name Lev)
Select ID PID Name @Lev From Tree Where (@Lev= and ((PID=@ID) or
(@ID is null and PID is null))) or (PID in (Select ID From @Tab Where Lev=@Lev))
order by ID
End
RETURN
END
GO
实际数据
Insert Tree(PID Name) values(null 公司)
Insert Tree(PID Name) values( IT)
Insert Tree(PID Name) values( Fin)
Insert Tree(PID Name) values( XZ)
Insert Tree(PID Name) values( HR)
GO
直接查询Select * from dbofGetTreeTable(null)就可以输入所有记录