SQL递归语句
USE [WPF] GO /****** Object: Table [dbo].[C_TREEVIEW] Script Date: 2019/10/7 12:48:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[C_TREEVIEW]( [GUID] [nchar](36) NULL, [ID] [int] NULL, [NAME] [nvarchar](100) NULL, [PID] [int] NULL ) ON [PRIMARY] GO INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'05f2c76c-6b43-4577-b72d-94669def2958', 0, N'根节点', -1) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'a308d38b-8d96-45ff-b7d3-978ecacb1419', 1, N'爷爷', 0) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'9b5f060a-ff40-4273-8c3d-a9c37056dcf5', 2, N'爸爸', 1) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'3a55b881-b6c7-4682-8d59-639a77066227', 3, N'大姑', 1) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'e27e364a-d28d-4607-b500-4522228775d6', 4, N'二姑', 1) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'9a2b4580-cc1e-49a9-98c8-2c7ede13091a', 5, N'三姑', 1) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'760e1af8-4962-464f-85bf-62ab817096ed', 6, N'姐姐', 2) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'f31f3456-77ff-44ee-9b0e-be788828ef22', 21, N'姜彦', 2) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'27868be6-b1fa-48c3-a8e9-8696fb65ef0a', 8, N'妹妹', 2) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'5d10b662-a87f-45e0-ba83-dbce90d31d83', 9, N'周鑫', 3) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'f835120a-2441-4b8e-a2ab-dd8f23accc2e', 10, N'慧丽', 3) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'b13fcf04-e920-4256-bfa8-b670821fcf5e', 11, N'志浩', 5) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'b917a081-9468-49c4-a27b-02e6685f9125', 12, N'夏慧', 4) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'1f242652-dd75-4cba-8081-8c3b23e492d0', 13, N'腾腾', 7) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'a1ca43a9-8742-44bd-ad7d-dcb595597c90', 14, N'睿聪', 6) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'ae9e82aa-8a6a-4054-8320-28f7c95e707f', 15, N'凌睿', 6) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'fa673193-46e0-4835-9763-29467537bddd', 16, N'蓬蓬', 8) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'a7e3974b-e663-44f2-b3fa-f7f4dd797bd3', 17, N'周鑫儿子', 9) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'3656c2a4-06b1-4256-89f7-75b4c6dcf0fd', 18, N'慧丽儿子', 10) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'7d51726f-4ccc-45d9-88db-3c88124a94e7', 19, N'夏慧儿子', 12) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'809794a5-0b51-4407-8740-6585d746f2a3', 20, N'志浩女儿', 11) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'aee1b0c4-a156-492b-92fb-453c96834e0c', 22, N'腾腾', 21) INSERT [dbo].[C_TREEVIEW] ([GUID], [ID], [NAME], [PID]) VALUES (N'fbe4c4ed-170c-4229-b6e8-ba6a0fa280d3', 23, N'苏苏', 21)
/****** Script for SelectTopNRows command from SSMS ******/ SELECT * FROM [WPF].[dbo].[C_TREEVIEW] UPDATE [WPF].[dbo].[C_TREEVIEW] SET PID=-1 WHERE ID=0 --1.递归有效语句1 with ts as ( select ID,NAME,PID from [WPF].[dbo].[C_TREEVIEW] where ID=2 and PID=1 --首先要查询出最原始父级的信息 union all --全连接 select a.ID, a.NAME,a.PID from [WPF].[dbo].[C_TREEVIEW] a inner join ts b on a.PID=b.ID --将整体的查询结果重新嵌套进语句中,将查询结果集中子类的ID与父类ID关联,进行查询 ) select * from ts order by ID --2.递归有效语句 2 with subqry(ID,NAME,PID) as ( select ID,NAME,PID from [WPF].[dbo].[C_TREEVIEW] where ID = 2 --指定id union all select [WPF].[dbo].[C_TREEVIEW].ID,[WPF].[dbo].[C_TREEVIEW].NAME,[WPF].[dbo].[C_TREEVIEW].PID from [WPF].[dbo].[C_TREEVIEW],subqry where [WPF].[dbo].[C_TREEVIEW].PID = subqry.ID ) select* from subqry --3.递归有效语句 3 WITH Emp AS ( SELECT * FROM [WPF].[dbo].[C_TREEVIEW] WHERE ID = 0 --首先要查询的父节点信息 UNION ALL --全连接 SELECT d.* FROM Emp INNER JOIN [WPF].[dbo].[C_TREEVIEW] d ON d.PID= Emp.ID ) SELECT * FROM Emp OPTION (MAXRECURSION 0);-- OPTION (MAXRECURSION 0);递归级数 0表示无限级,1:一层;2:两层... --4.递归有效语句 4 --https://so.m.sm.cn/c/www.360doc.cn/mip/373486751.html with my1 as ( select * from [WPF].[dbo].[C_TREEVIEW] where ID = 21 union all select [WPF].[dbo].[C_TREEVIEW].* from my1, [WPF].[dbo].[C_TREEVIEW] where my1.ID = [WPF].[dbo].[C_TREEVIEW].PID ) select * from my1 where my1.ID<>21--结果包含21这条记录,如果不想包含,可以在最后加上:where id <> 21 --5.递归删除语句有点问题版本 删除了 仅当前节点+仅其父节点 共计2条数据 with my1 as ( select * from [WPF].[dbo].[C_TREEVIEW] where ID = 24 union all select [WPF].[dbo].[C_TREEVIEW].* from my1, [WPF].[dbo].[C_TREEVIEW] where my1.ID = [WPF].[dbo].[C_TREEVIEW].PID ) delete from [WPF].[dbo].[C_TREEVIEW] where exists (select ID from my1 where my1.PID = [WPF].[dbo].[C_TREEVIEW].ID) SELECT * FROM [WPF].[dbo].[C_TREEVIEW] --3.递归有效语句 3 WITH Emp AS ( SELECT * FROM [WPF].[dbo].[C_TREEVIEW] WHERE ID = 21 --首先要查询的父节点信息 UNION ALL --全连接 SELECT d.* FROM Emp INNER JOIN [WPF].[dbo].[C_TREEVIEW] d ON d.PID= Emp.ID ) SELECT * FROM Emp OPTION (MAXRECURSION 0);-- OPTION (MAXRECURSION 0);递归级数 0表示无限级,1:一层;2:两层... --6.递归删除语句 删除该节点下面所有的子节点(不删除当前节点) with my1 as ( select * from [WPF].[dbo].[C_TREEVIEW] where ID = 24 union all select [WPF].[dbo].[C_TREEVIEW].* from my1, [WPF].[dbo].[C_TREEVIEW] where my1.ID = [WPF].[dbo].[C_TREEVIEW].PID ) delete from [WPF].[dbo].[C_TREEVIEW] where exists (select ID from my1 where my1.ID = [WPF].[dbo].[C_TREEVIEW].PID) --7.递归删除语句 删除该节点下面所有的子节点(同时删除当前节点) with my1 as ( select * from [WPF].[dbo].[C_TREEVIEW] where ID = 24 union all select [WPF].[dbo].[C_TREEVIEW].* from my1, [WPF].[dbo].[C_TREEVIEW] where my1.ID = [WPF].[dbo].[C_TREEVIEW].PID ) delete from [WPF].[dbo].[C_TREEVIEW] where exists (select ID from my1 where my1.ID = [WPF].[dbo].[C_TREEVIEW].ID ) --设置递归级数 ;WITH t AS ( SELECT 1 AS num UNION ALL SELECT num+1 FROM t WHERE num<200 ) SELECT * FROM t OPTION(MAXRECURSION 0)
![](https://files-cdn.cnblogs.com/files/jiangyan219/Alipay.bmp)
您的资助是我最大的动力!
金额随意,欢迎来赏!
![](https://files-cdn.cnblogs.com/files/jiangyan219/WeChat.bmp)
我写的东西能让你能懂,那是义务
毕竟占用了你生命中的宝贵的时间和注意力
要是你还能喜欢我的作品,那就是缘分了
如果,您希望更容易地发现我的新博客,不妨点击一下绿色通道的因为,我的写作热情也离不开您的肯定支持,感谢您的阅读,我是【青青子衿】!