简单查询树形结构数据库表

kider

作者: 简单, 　出处:博客园

　　在与同事Rock讨论中，得到一个思路，不用嵌套调用直接用一个语句就可以生成树，实事上他已经做好了该程序，他用了临时表写了一个存储过程，我改写为一个表值函数，供大家参考：
　　表结构及表值函数如下：
　　查询树表语句

1. 1/**//****** Object: Table [dbo].[Tree] Script Date: 2005-11-04 18:07:00 ******/
   
2. 2CREATE TABLE [dbo].[Tree] (
   
3. 3 [ID] [int] IDENTITY (1, 1) NOT NULL ,
   
4. 4 [PID] [int] NULL ,
   
5. 5 [Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL
   
6. 6) ON [PRIMARY]
   
7. 7GO
   
8. 8
   
9. 9 CREATE CLUSTERED INDEX [IX_Tree] ON [dbo].[Tree]([PID]) ON [PRIMARY]
   
10. 10GO
    
11. 11
    
12. 12ALTER TABLE [dbo].[Tree] WITH NOCHECK ADD
    
13. 13 CONSTRAINT [PK_Tree] PRIMARY KEY NONCLUSTERED
    
14. 14 (
    
15. 15 [ID]
    
16. 16 ) ON [PRIMARY] ,
    
17. 17 CONSTRAINT [子ID不能等于父ID] CHECK ([ID] <> [PID])
    
18. 18GO
    
19. 19
    
20. 20ALTER TABLE [dbo].[Tree] ADD
    
21. 21 CONSTRAINT [FK_Tree_Tree] FOREIGN KEY
    
22. 22 (
    
23. 23 [PID]
    
24. 24 ) REFERENCES [dbo].[Tree] (
    
25. 25 [ID]
    
26. 26 )
    
27. 27GO
    
28. 28
    
29. 29/**//****** 对象: 用户定义的函数 dbo.fGetTreeTable 脚本日期: 2005-11-04 18:07:02 ******/
    
30. 30CREATE FUNCTION dbo.fGetTreeTable
    
31. 31 (
    
32. 32 @ID int= null
    
33. 33 )
    
34. 34RETURNS @Tab TABLE(ID int, PID int, Name varchar(10), Lev int)
    
35. 35AS
    
36. 36 BEGIN
    
37. 37 Declare @lev int
    
38. 38 Set @lev=0
    
39. 39
    
40. 40 While @lev=0 or @@ROWCount>0
    
41. 41 Begin
    
42. 42 Set @Lev=@Lev+1
    
43. 43 Insert @Tab(ID, PID, Name, Lev)
    
44. 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. 45 order by ID
    
46. 46 End
    
47. 47 RETURN
    
48. 48 END
    
49. 49
    
50. 50GO
    
51. 51
    
52. 52--实际数据
    
53. 53Insert Tree(PID, Name) values(null, 公司)
    
54. 54Insert Tree(PID, Name) values(3, IT)
    
55. 55Insert Tree(PID, Name) values(1, Fin)
    
56. 56Insert Tree(PID, Name) values(5, XZ)
    
57. 57Insert Tree(PID, Name) values(1, HR)
    
58. 58GO

复制代码

直接查询Select * from dbo.fGetTreeTable(null)就可以输入所有记录。