SQL递归查询-白红宇

SQL递归查询

阅读量：5051 次

发布时间：2019-06-12

本文共 11660 字，大约阅读时间需要 38 分钟。

使用场景：

A推荐了3个人分别是：a,b,c

a又推荐了三个人分别是：a1,a2,a3

b又推荐了三个人分别时：b1,b2,b3

c又推荐了三个人分别时：c1,c2,c3

现在要查询A的下线一共有多少人

sql代码：

with cte(MemId,MemCard,MemName,MemMobile,MemRecommendID) as (--父项 select MemId,MemCard,MemName,MemMobile,MemRecommendID from dbo.Mem where MemRecommendID = 2 union all --递归结果集中的下级 select t.MemId,t.MemCard,t.MemName,t.MemMobile,t.MemRecommendID from Mem as t inner join cte as c on t.MemRecommendID = c.MemId ) select MemId,MemCard,MemName,MemMobile,MemRecommendID from cte

表的语句：

CREATE TABLE [dbo].[Mem](    [MemID] [int] IDENTITY(1,1) NOT NULL,    [MemCard] [varchar](50) NULL,    [MemPassword] [nvarchar](50) NULL,    [MemName] [nvarchar](50) NULL,    [MemSex] [int] NULL,    [MemIdentityCard] [varchar](50) NULL,    [MemMobile] [varchar](50) NULL,    [MemPhoto] [nvarchar](200) NULL,    [MemBirthdayType] [bit] NULL,    [MemBirthday] [datetime] NULL,    [MemIsPast] [bit] NULL,    [MemPastTime] [datetime] NULL,    [MemPoint] [int] NULL,    [MemPointAutomatic] [bit] NULL,    [MemMoney] [money] NULL,    [MemConsumeMoney] [money] NULL,    [MemConsumeLastTime] [datetime] NULL,    [MemConsumeCount] [int] NULL,    [MemEmail] [varchar](50) NULL,    [MemAddress] [nvarchar](200) NULL,    [MemState] [int] NULL,    [MemRecommendID] [int] NULL,    [MemLevelID] [int] NULL,    [MemShopID] [int] NULL,    [MemCreateTime] [datetime] NULL,    [MemRemark] [nvarchar](500) NULL,    [MemUserID] [int] NULL,    [MemTelePhone] [varchar](50) NULL,    [MemQRCode] [varchar](500) NULL,    [MemProvince] [varchar](50) NULL,    [MemCity] [varchar](50) NULL,    [MemCounty] [varchar](50) NULL,    [MemVillage] [varchar](50) NULL,    [MemQuestion] [varchar](500) NULL,    [MemAnswer] [varchar](500) NULL,    [MemWeiXinCard] [nvarchar](50) NULL,    [MemCardNumber] [varchar](50) NULL,    [MemAttention] [int] NULL,    [MemWeiXinCards] [nvarchar](50) NULL,    [C_bm] [varchar](255) NULL,    [C_yuechu] [varchar](255) NULL,    [C_yuechu1] [varchar](255) NULL,    [C_cph] [varchar](255) NULL,    [C_123] [varchar](255) NULL,    [C_maibo] [varchar](255) NULL,    [C_C_tiwen] [varchar](255) NULL,    [C_5442454] [varchar](255) NULL,    [C_ah] [varchar](255) NULL,    [C_C_htzt] [varchar](255) NULL, CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED (    [MemID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[Mem] ADD  CONSTRAINT [DF_Mem_MemPoint]  DEFAULT ((0)) FOR [MemPoint]GOALTER TABLE [dbo].[Mem] ADD  CONSTRAINT [DF_Mem_MemMoney]  DEFAULT ((0)) FOR [MemMoney]GOALTER TABLE [dbo].[Mem] ADD  CONSTRAINT [DF_Mem_MemAttention]  DEFAULT ((2)) FOR [MemAttention]GO

给表插入数据：

GO/****** Object:  Table [dbo].[Mem]    Script Date: 06/22/2018 14:39:52 ******/SET IDENTITY_INSERT [dbo].[Mem] ONINSERT [dbo].[Mem] ([MemID], [MemCard], [MemPassword], [MemName], [MemSex], [MemIdentityCard], [MemMobile], [MemPhoto], [MemBirthdayType], [MemBirthday], [MemIsPast], [MemPastTime], [MemPoint], [MemPointAutomatic], [MemMoney], [MemConsumeMoney], [MemConsumeLastTime], [MemConsumeCount], [MemEmail], [MemAddress], [MemState], [MemRecommendID], [MemLevelID], [MemShopID], [MemCreateTime], [MemRemark], [MemUserID], [MemTelePhone], [MemQRCode], [MemProvince], [MemCity], [MemCounty], [MemVillage], [MemQuestion], [MemAnswer], [MemWeiXinCard], [MemCardNumber], [MemAttention], [MemWeiXinCards], [C_bm], [C_yuechu], [C_yuechu1], [C_cph], [C_123], [C_maibo], [C_C_tiwen], [C_5442454], [C_ah], [C_C_htzt]) VALUES (0, N'0', N'E62A9E6C1892C6BB', N'散客', 1, NULL, NULL, NULL, NULL, CAST(0x0000000000000000 AS DateTime), 0, CAST(0x000592BB00000000 AS DateTime), 0, NULL, 0.0000, NULL, NULL, NULL, NULL, NULL, 0, NULL, -1, 1, CAST(0x0000A1A600000000 AS DateTime), NULL, 1, NULL, N'', N'', N'', N'', N'', N'', N'', N'', N'', 2, N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Mem] ([MemID], [MemCard], [MemPassword], [MemName], [MemSex], [MemIdentityCard], [MemMobile], [MemPhoto], [MemBirthdayType], [MemBirthday], [MemIsPast], [MemPastTime], [MemPoint], [MemPointAutomatic], [MemMoney], [MemConsumeMoney], [MemConsumeLastTime], [MemConsumeCount], [MemEmail], [MemAddress], [MemState], [MemRecommendID], [MemLevelID], [MemShopID], [MemCreateTime], [MemRemark], [MemUserID], [MemTelePhone], [MemQRCode], [MemProvince], [MemCity], [MemCounty], [MemVillage], [MemQuestion], [MemAnswer], [MemWeiXinCard], [MemCardNumber], [MemAttention], [MemWeiXinCards], [C_bm], [C_yuechu], [C_yuechu1], [C_cph], [C_123], [C_maibo], [C_C_tiwen], [C_5442454], [C_ah], [C_C_htzt]) VALUES (1, N'10086', N'2CBE9C73F856E743', N'123', 0, N'', N'15236548523', N'', 1, CAST(0x0000000000000000 AS DateTime), 0, CAST(0x000592BB00000000 AS DateTime), 4, 1, 0.0000, 0.0000, NULL, NULL, N'', N'', 0, 0, 0, 1, CAST(0x0000A90500000000 AS DateTime), N'', 1, N'', N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Mem] ([MemID], [MemCard], [MemPassword], [MemName], [MemSex], [MemIdentityCard], [MemMobile], [MemPhoto], [MemBirthdayType], [MemBirthday], [MemIsPast], [MemPastTime], [MemPoint], [MemPointAutomatic], [MemMoney], [MemConsumeMoney], [MemConsumeLastTime], [MemConsumeCount], [MemEmail], [MemAddress], [MemState], [MemRecommendID], [MemLevelID], [MemShopID], [MemCreateTime], [MemRemark], [MemUserID], [MemTelePhone], [MemQRCode], [MemProvince], [MemCity], [MemCounty], [MemVillage], [MemQuestion], [MemAnswer], [MemWeiXinCard], [MemCardNumber], [MemAttention], [MemWeiXinCards], [C_bm], [C_yuechu], [C_yuechu1], [C_cph], [C_123], [C_maibo], [C_C_tiwen], [C_5442454], [C_ah], [C_C_htzt]) VALUES (2, N'1008601', N'E62A9E6C1892C6BB', N'123', 0, N'', N'15236548513', N'', 1, CAST(0x0000000000000000 AS DateTime), 0, CAST(0x000592BB00000000 AS DateTime), 0, 1, 0.0000, 16.0000, CAST(0x0000A905012660C9 AS DateTime), 1, N'', N'', 0, 1, 0, 1, CAST(0x0000A90500000000 AS DateTime), N'', 1, N'', N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Mem] ([MemID], [MemCard], [MemPassword], [MemName], [MemSex], [MemIdentityCard], [MemMobile], [MemPhoto], [MemBirthdayType], [MemBirthday], [MemIsPast], [MemPastTime], [MemPoint], [MemPointAutomatic], [MemMoney], [MemConsumeMoney], [MemConsumeLastTime], [MemConsumeCount], [MemEmail], [MemAddress], [MemState], [MemRecommendID], [MemLevelID], [MemShopID], [MemCreateTime], [MemRemark], [MemUserID], [MemTelePhone], [MemQRCode], [MemProvince], [MemCity], [MemCounty], [MemVillage], [MemQuestion], [MemAnswer], [MemWeiXinCard], [MemCardNumber], [MemAttention], [MemWeiXinCards], [C_bm], [C_yuechu], [C_yuechu1], [C_cph], [C_123], [C_maibo], [C_C_tiwen], [C_5442454], [C_ah], [C_C_htzt]) VALUES (3, N'100860101', N'E62A9E6C1892C6BB', N'', 0, N'', N'15236458951', N'', 1, CAST(0x0000000000000000 AS DateTime), 0, CAST(0x000592BB00000000 AS DateTime), 2, 1, 0.0000, 1.6000, CAST(0x0000A905012CF343 AS DateTime), 1, N'', N'', 0, 2, 0, 1, CAST(0x0000A90500000000 AS DateTime), N'', 1, N'', N'', N'', N'', N'', N'', NULL, NULL, NULL, N'', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Mem] ([MemID], [MemCard], [MemPassword], [MemName], [MemSex], [MemIdentityCard], [MemMobile], [MemPhoto], [MemBirthdayType], [MemBirthday], [MemIsPast], [MemPastTime], [MemPoint], [MemPointAutomatic], [MemMoney], [MemConsumeMoney], [MemConsumeLastTime], [MemConsumeCount], [MemEmail], [MemAddress], [MemState], [MemRecommendID], [MemLevelID], [MemShopID], [MemCreateTime], [MemRemark], [MemUserID], [MemTelePhone], [MemQRCode], [MemProvince], [MemCity], [MemCounty], [MemVillage], [MemQuestion], [MemAnswer], [MemWeiXinCard], [MemCardNumber], [MemAttention], [MemWeiXinCards], [C_bm], [C_yuechu], [C_yuechu1], [C_cph], [C_123], [C_maibo], [C_C_tiwen], [C_5442454], [C_ah], [C_C_htzt]) VALUES (4, N'100860102', N'E62A9E6C1892C6BB', N'234', 0, N'', N'15236548569', N'', 1, CAST(0x0000000000000000 AS DateTime), 0, CAST(0x000592BB00000000 AS DateTime), 0, 1, 0.0000, NULL, NULL, NULL, N'', N'', 0, 1, 0, 1, CAST(0x0000A90500000000 AS DateTime), N'', 1, N'', N'', N'', N'', N'', N'', NULL, NULL, NULL, N'', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Mem] ([MemID], [MemCard], [MemPassword], [MemName], [MemSex], [MemIdentityCard], [MemMobile], [MemPhoto], [MemBirthdayType], [MemBirthday], [MemIsPast], [MemPastTime], [MemPoint], [MemPointAutomatic], [MemMoney], [MemConsumeMoney], [MemConsumeLastTime], [MemConsumeCount], [MemEmail], [MemAddress], [MemState], [MemRecommendID], [MemLevelID], [MemShopID], [MemCreateTime], [MemRemark], [MemUserID], [MemTelePhone], [MemQRCode], [MemProvince], [MemCity], [MemCounty], [MemVillage], [MemQuestion], [MemAnswer], [MemWeiXinCard], [MemCardNumber], [MemAttention], [MemWeiXinCards], [C_bm], [C_yuechu], [C_yuechu1], [C_cph], [C_123], [C_maibo], [C_C_tiwen], [C_5442454], [C_ah], [C_C_htzt]) VALUES (5, N'100860105', N'E62A9E6C1892C6BB', N'45', 0, N'', N'15236548526', N'', 1, CAST(0x0000000000000000 AS DateTime), 0, CAST(0x000592BB00000000 AS DateTime), 0, 1, 0.0000, NULL, NULL, NULL, N'', N'', 0, 2, 0, 1, CAST(0x0000A90500000000 AS DateTime), N'', 1, N'', N'', N'', N'', N'', N'', NULL, NULL, NULL, N'', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Mem] ([MemID], [MemCard], [MemPassword], [MemName], [MemSex], [MemIdentityCard], [MemMobile], [MemPhoto], [MemBirthdayType], [MemBirthday], [MemIsPast], [MemPastTime], [MemPoint], [MemPointAutomatic], [MemMoney], [MemConsumeMoney], [MemConsumeLastTime], [MemConsumeCount], [MemEmail], [MemAddress], [MemState], [MemRecommendID], [MemLevelID], [MemShopID], [MemCreateTime], [MemRemark], [MemUserID], [MemTelePhone], [MemQRCode], [MemProvince], [MemCity], [MemCounty], [MemVillage], [MemQuestion], [MemAnswer], [MemWeiXinCard], [MemCardNumber], [MemAttention], [MemWeiXinCards], [C_bm], [C_yuechu], [C_yuechu1], [C_cph], [C_123], [C_maibo], [C_C_tiwen], [C_5442454], [C_ah], [C_C_htzt]) VALUES (6, N'10086010501', N'E62A9E6C1892C6BB', N'435646', 0, N'', N'15236548549', N'', 1, CAST(0x0000000000000000 AS DateTime), 0, CAST(0x000592BB00000000 AS DateTime), 0, 1, 0.0000, 0.0000, NULL, NULL, N'', N'', 0, 999999, 0, 1, CAST(0x0000A90500000000 AS DateTime), N'', 1, N'', N'', NULL, NULL, NULL, NULL, NULL, NULL, NULL, N'', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)INSERT [dbo].[Mem] ([MemID], [MemCard], [MemPassword], [MemName], [MemSex], [MemIdentityCard], [MemMobile], [MemPhoto], [MemBirthdayType], [MemBirthday], [MemIsPast], [MemPastTime], [MemPoint], [MemPointAutomatic], [MemMoney], [MemConsumeMoney], [MemConsumeLastTime], [MemConsumeCount], [MemEmail], [MemAddress], [MemState], [MemRecommendID], [MemLevelID], [MemShopID], [MemCreateTime], [MemRemark], [MemUserID], [MemTelePhone], [MemQRCode], [MemProvince], [MemCity], [MemCounty], [MemVillage], [MemQuestion], [MemAnswer], [MemWeiXinCard], [MemCardNumber], [MemAttention], [MemWeiXinCards], [C_bm], [C_yuechu], [C_yuechu1], [C_cph], [C_123], [C_maibo], [C_C_tiwen], [C_5442454], [C_ah], [C_C_htzt]) VALUES (7, N'15236', N'E62A9E6C1892C6BB', N'876', 0, N'', N'15856236548', N'', 1, CAST(0x0000000000000000 AS DateTime), 0, CAST(0x000592BB00000000 AS DateTime), 0, 1, 0.0000, NULL, NULL, NULL, N'', N'', 0, 5, 0, 1, CAST(0x0000A90500000000 AS DateTime), N'', 1, N'', N'', N'', N'', N'', N'', NULL, NULL, NULL, N'', 2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)SET IDENTITY_INSERT [dbo].[Mem] OFF

查询结果如下：