SQL递归查询

使用场景：

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]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Mem] ADD  CONSTRAINT [DF_Mem_MemPoint]  DEFAULT ((0)) FOR [MemPoint]
GO

ALTER TABLE [dbo].[Mem] ADD  CONSTRAINT [DF_Mem_MemMoney]  DEFAULT ((0)) FOR [MemMoney]
GO

ALTER 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] ON
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 (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

查询结果如下：

posted @ 2018-06-22 14:42 风琴~云淡阅读(461) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

奈何缘浅丶世俗沾染了无奈

SQL递归查询

公告