CTE實現嵌套遞迴

[ WITH <common_table_expression> [ ,n ] ]<common_table_expression>::=        expression_name [ ( column_name [ ,n ] ) ]    AS (       CTE_query_definition1  --  錨點成員       union all       CTE_query_definition2  --  遞迴成員    )

 以交易品資料庫中地區資訊為例：

建立表：

CREATE TABLE [dbo].[AreaInfo]([AreaID] [int] IDENTITY(1,1) NOT NULL,[AreaName] [nvarchar](50) NOT NULL,[ShowOrder] [int] NULL,[ParentID] [int] NULL,[PinYin] [nvarchar](50) NULL,PRIMARY KEY CLUSTERED ([AreaID] 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

 插入資料：

INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (12, N'安徽', 1, 0, N'anhui')INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (117, N'合肥', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (118, N'蕪湖', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (119, N'蚌埠', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (120, N'淮南', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (121, N'馬鞍山', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (122, N'淮北', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (123, N'銅陵', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (124, N'安慶', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (125, N'黃山', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (126, N'滁州', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (127, N'阜陽', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (128, N'宿州', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (129, N'巢湖', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (130, N'六安', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (131, N'亳州', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (132, N'池州', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (133, N'宣城', 1, 12, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1310, N'宣州區', 1, 133, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1311, N'郎溪縣', 1, 133, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1312, N'廣德縣', 1, 133, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1313, N'涇縣', 1, 133, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1314, N'績溪縣', 1, 133, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1315, N'旌德縣', 1, 133, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1316, N'寧國市', 1, 133, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1306, N'貴池區', 1, 132, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1307, N'東至縣', 1, 132, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1308, N'石台縣', 1, 132, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1309, N'青陽縣', 1, 132, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1302, N'譙城區', 1, 131, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1303, N'渦陽縣', 1, 131, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1304, N'蒙城縣', 1, 131, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1305, N'利辛縣', 1, 131, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1295, N'金安區', 1, 130, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1296, N'裕安區', 1, 130, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1297, N'壽縣', 1, 130, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1298, N'霍邱縣', 1, 130, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1299, N'舒城縣', 1, 130, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1300, N'金寨縣', 1, 130, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1301, N'霍山縣', 1, 130, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1290, N'居巢區', 1, 129, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1291, N'廬江縣', 1, 129, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1292, N'無為縣', 1, 129, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1293, N'含山縣', 1, 129, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1294, N'和縣 ', 1, 129, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1285, N'埇橋區', 1, 128, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1286, N'碭山縣', 1, 128, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1287, N'蕭縣', 1, 128, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1288, N'靈璧縣', 1, 128, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1289, N'泗縣 ', 1, 128, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1277, N'潁州區', 1, 127, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1278, N'潁東區', 1, 127, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1279, N'潁泉區', 1, 127, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1280, N'臨泉縣', 1, 127, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1281, N'太和縣', 1, 127, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1282, N'阜南縣', 1, 127, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1283, N'潁上縣', 1, 127, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1284, N'界首市', 1, 127, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1269, N'琅琊區', 1, 126, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1270, N'南譙區', 1, 126, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1271, N'來安縣', 1, 126, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1272, N'全椒縣', 1, 126, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1273, N'定遠縣', 1, 126, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1274, N'鳳陽縣', 1, 126, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1275, N'天長市', 1, 126, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1276, N'明光市', 1, 126, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1262, N'屯溪區', 1, 125, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1263, N'黃山區', 1, 125, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1264, N'徽州區', 1, 125, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1265, N'歙縣', 1, 125, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1266, N'休寧縣', 1, 125, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1267, N'黟縣', 1, 125, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1268, N'祁門縣', 1, 125, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1251, N'迎江區', 1, 124, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1252, N'大觀區', 1, 124, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1253, N'宜秀區', 1, 124, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1254, N'懷寧縣', 1, 124, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1255, N'樅陽縣', 1, 124, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1256, N'潛山縣', 1, 124, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1257, N'太湖縣', 1, 124, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1258, N'宿松縣', 1, 124, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1259, N'望江縣', 1, 124, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1260, N'嶽西縣', 1, 124, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1261, N'桐城市', 1, 124, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1248, N'銅官山區', 1, 123, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1249, N'獅子山區', 1, 123, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1250, N'銅陵縣', 1, 123, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1244, N'杜集區', 1, 122, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1245, N'相山區', 1, 122, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1246, N'烈山區', 1, 122, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1247, N'濉溪縣 ', 1, 122, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1240, N'金家莊區', 1, 121, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1241, N'花山區', 1, 121, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1242, N'雨山區', 1, 121, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1243, N'當塗縣', 1, 121, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1234, N'大通區', 1, 120, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1235, N'田家庵區', 1, 120, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1236, N'謝家集區', 1, 120, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1237, N'八公山區', 1, 120, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1238, N'潘集區', 1, 120, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1239, N'鳳台縣', 1, 120, NULL)GOprint 'Processed 100 total records'INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1227, N'龍子湖區', 1, 119, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1228, N'蚌山區', 1, 119, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1229, N'禹會區', 1, 119, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1230, N'淮上區', 1, 119, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1231, N'懷遠縣', 1, 119, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1232, N'五河縣', 1, 119, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1233, N'固鎮縣', 1, 119, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1220, N'鏡湖區', 1, 118, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1221, N'弋江區', 1, 118, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1222, N'鳩江區', 1, 118, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1223, N'三山區', 1, 118, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1224, N'蕪湖縣', 1, 118, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1225, N'繁昌縣', 1, 118, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1226, N'南陵縣', 1, 118, NULL)INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1213, N'瑤海', 1, 117, N'yao hai')INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1214, N'廬陽', 1, 117, N'lu yang')INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1215, N'蜀山', 1, 117, N'shu shan')INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1216, N'包河', 1, 117, N'bao he')INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1217, N'長豐', 1, 117, N'chang feng')INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1218, N'肥東', 1, 117, N'fei dong')INSERT [dbo].[AreaInfo] ([AreaID], [AreaName], [ShowOrder], [ParentID], [PinYin]) VALUES (1219, N'肥西', 1, 117, N'fei xi')

 現在需求如下：

查詢  安徽省下面的所有城市資訊(一級城市，二級城市)

現在利用CTE來實現此種功能：

  WITH temp1 AS(   SELECT * FROM AreaInfo WHERE AreaName ='安徽'   UNION ALL   SELECT aa.*  FROM AreaInfo aa , temp1 tt where aa.ParentID = tt.AreaID  )  select  * from temp1

 利用cte來實現查詢但是不是葉子節點城市：

 WITH temp2 AS(   SELECT * FROM AreaInfo WHERE AreaName ='安徽'   UNION ALL   SELECT aa.*  FROM AreaInfo aa , temp2 tt where aa.ParentID = tt.AreaID  ),  temp3 as(  select * from temp2 tt where tt.AreaID in (select t.ParentID from temp2 t )  )  select * from temp3