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)
GO
print '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