简单查询树形结构数据库表是什么
表结构及表值函数如下： 查询树表语句 1/**//**** Object: Table [dbo]。[Tree] Script Date: 2005-11-04 18:07:00 ***/ 2CREATE TABLE [dbo]。 [Tree] ( 3 [ID] [int] IDENTITY (1, 1) NOT NULL , 4 [PID] [int] NULL , 5 [Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL 6) ON [PRIMARY] 7GO 8 9 CREATE CLUSTERED INDEX [...全部
表结构及表值函数如下： 查询树表语句 1/**//**** Object: Table [dbo]。[Tree] Script Date: 2005-11-04 18:07:00 ***/ 2CREATE TABLE [dbo]。
[Tree] ( 3 [ID] [int] IDENTITY (1, 1) NOT NULL , 4 [PID] [int] NULL , 5 [Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL 6) ON [PRIMARY] 7GO 8 9 CREATE CLUSTERED INDEX [IX_Tree] ON [dbo]。
[Tree]([PID]) ON [PRIMARY]10GO1112ALTER TABLE [dbo]。[Tree] WITH NOCHECK ADD 13 CONSTRAINT [PK_Tree] PRIMARY KEY NONCLUSTERED 14 (15 [ID]16 ) ON [PRIMARY] ,17 CONSTRAINT [子ID不能等于父ID] CHECK ([ID] [PID])18GO1920ALTER TABLE [dbo]。
[Tree] ADD 21 CONSTRAINT [FK_Tree_Tree] FOREIGN KEY 22 (23 [PID]24 ) REFERENCES [dbo]。[Tree] (25 [ID]26 )27GO2829/**//****** 对象: 用户定义的函数 dbo。
fGetTreeTable 脚本日期: 2005-11-04 18:07:02 ******/30CREATE FUNCTION dbo。fGetTreeTable31 (32 @ID int= null33 )34RETURNS @Tab TABLE(ID int, PID int, Name varchar(10), Lev int)35AS36 BEGIN37 Declare @lev int38 Set @lev=039 40 While @lev=0 or @@ROWCount>041 Begin42 Set @Lev=@Lev+143 Insert @Tab(ID, PID, Name, Lev)44 Select ID, PID, Name, @Lev From Tree Where (@Lev=1 and ((PID=@ID) or (@ID is null and PID is null))) or (PID in (Select ID From @Tab Where Lev=@Lev-1))45 order by ID46 End47 RETURN48 END4950GO5152--实际数据53Insert Tree(PID, Name) values(null, 公司)54Insert Tree(PID, Name) values(3, IT)55Insert Tree(PID, Name) values(1, Fin)56Insert Tree(PID, Name) values(5, XZ)57Insert Tree(PID, Name) values(1, HR)58GO 直接查询Select * from dbo。
fGetTreeTable（null）就可以输入所有记录。
完成 丢弃。收起
