SQL递归函数列出父级的所有子级(ID ParentID模式)

落日映苍穹つ 2022-01-06 05:25 320阅读 0赞

1.  \--调用方法：
2.  \--select \* from GetChild('24')
3.  \--select id from GetChild('24')
4.  \--select \* from KuCun where ProductType in(select id from GetChild('24'))
5.  
6.  Create function \[dbo\].\[GetChild\](@ID varchar(10))
7.  returns @t table(ID varchar(10),ParentID varchar(10),Level int)
8.  as
9.  begin
10. declare @i int
11. set @i = 1
12. insert into @t select @ID,@ID,0 \--当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作
13. insert into @t select ID,ParentID,@i from Dept where ParentID = @ID
14. 
15. while @@rowcount<>0
16. begin
17. set @i = @i + 1
18. insert into @t
19. select
20. a.ID,a.ParentID,@i
21. from
22. Dept a,@t b
23. where
24. a.ParentID=b.ID and b.Level = @i-1
25. end
26. return
27. end
28. 
29. \--------------------------------------------------------------------------------
30. \--在SQL Server2005中其实提供了CTE\[公共表表达式\]来实现递归: 
31. Declare @Id Int
32. Set @Id = 24; \---在此修改父节点 
33. 
34. With RootNodeCTE(Id,ParentId)
35. As
36. (
37. Select Id,ParentId From Dept Where ParentId In (@Id)
38. Union All
39. Select Dept.Id,Dept.ParentId From RootNodeCTE
40. Inner Join Dept
41. On RootNodeCTE.Id = Dept.ParentId
42. )
43. 
44. Select \* From RootNodeCTE
45. 
46. \--------------------------------------------------------------------------------
47. \--------------------------------------------------------------------------------
48. \--------------------------------------------------------------------------------
49. 
50. \--生成测试数据
51. create table Dept(ID int,ParentID int,msg varchar(20))
52. insert into Dept select 1,0,null
53. insert into Dept select 2,1,null
54. insert into Dept select 3,1,null
55. insert into Dept select 4,2,null
56. insert into Dept select 5,3,null
57. insert into Dept select 6,5,null
58. insert into Dept select 7,6,null
59. go
60. 
61. 
62. \--创建用户定义函数
63. Create function \[dbo\].\[GetChild\](@ID varchar(10))
64. returns @t table(ID varchar(10),ParentID varchar(10),Level int)
65. as
66. begin
67. declare @i int
68. set @i = 1
69. insert into @t select @ID,@ID,0 \--当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作
70. insert into @t select ID,ParentID,@i from Dept where ParentID = @ID
71. 
72. while @@rowcount<>0
73. begin
74. set @i = @i + 1
75. insert into @t
76. select
77. a.ID,a.ParentID,@i
78. from
79. Dept a,@t b
80. where
81. a.ParentID=b.ID and b.Level = @i-1
82. end
83. return
84. end
85. 
86. 
87. \--执行查询
88. select ID from dbo.GetChild(3)
89. go
90. 
91. \--输出结果
92. /\*
93. 3
94. 5
95. 6
96. 7
97. \*/
98. 
99. \--删除测试数据
100. drop function GetChild
101. drop table Dept
102. 
103. \--http://topic.csdn.net/u/20080409/16/1fb7d941-b1a1-4326-a936-230ddf057cbe.html

转载于:https://www.cnblogs.com/zhangchenliang/archive/2012/03/12/2391383.html