首页 / SQL Server / 正文

[SQL]SQL Server数据库使用CTE递归查询树形结构的层级深度实现语句

2889 发布于: 2018-06-25 读完约需62分钟

在以树形结构的数据库表设计中，我们可以使用SQL Server的CTE语句实现递归，来查询每条数据在树形节点中的层级深度，示例数据的表结构如下：

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Area]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Area](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AreaCode] [nvarchar](6) NULL,
    [AreaName] [nvarchar](50) NULL,
    [ParentId] [int] NULL,
    [ParentCode] [nvarchar](6) NULL,
    [Level] [int] NULL,
    [Sort] [int] NULL,
    [CreatedOn] [datetime] NULL,
 CONSTRAINT [PK_Area] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END

示例用到示例数据如下（当然了，此示例数据中，我已经将层级深度计算出来的，阅读时，大家可以忽略数据表中的Level列）：

INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (1, N'000000', N'中国', 0, NULL, 1, 0, CAST(N'2018-06-25T17:31:12.253' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (2, N'110000', N'北京市', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.273' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (3, N'120000', N'天津市', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (4, N'130000', N'河北省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (5, N'140000', N'山西省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (6, N'150000', N'内蒙古自治区', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (7, N'210000', N'辽宁省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (8, N'220000', N'吉林省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (9, N'230000', N'黑龙江省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (10, N'310000', N'上海市', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (11, N'320000', N'江苏省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (12, N'330000', N'浙江省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (13, N'340000', N'安徽省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (14, N'350000', N'福建省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (15, N'360000', N'江西省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (16, N'370000', N'山东省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (17, N'410000', N'河南省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.283' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (18, N'420000', N'湖北省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (19, N'430000', N'湖南省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (20, N'440000', N'广东省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (21, N'450000', N'广西壮族自治区', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (22, N'460000', N'海南省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (23, N'500000', N'重庆市', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (24, N'510000', N'四川省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (25, N'520000', N'贵州省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (26, N'530000', N'云南省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (27, N'540000', N'西藏自治区', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (28, N'610000', N'陕西省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (29, N'620000', N'甘肃省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (30, N'630000', N'青海省', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (31, N'640000', N'宁夏回族自治区', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (32, N'650000', N'新疆维吾尔自治区', 1, N'000000', 2, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (33, N'110100', N'北京市', 2, N'110000', 3, 0, CAST(N'2018-06-25T17:31:12.293' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (34, N'110101', N'昌平区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.333' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (35, N'110102', N'朝阳区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.333' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (36, N'110103', N'崇文区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.343' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (37, N'110104', N'大兴区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.343' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (38, N'110105', N'东城区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.343' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (39, N'110106', N'房山区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.343' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (40, N'110107', N'丰台区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.353' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (41, N'110108', N'海淀区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.353' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (42, N'110109', N'怀柔区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.353' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (43, N'110110', N'门头沟区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.353' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (44, N'110111', N'密云县', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.353' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (45, N'110112', N'平谷区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.363' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (46, N'110113', N'石景山区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.363' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (47, N'110114', N'顺义区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.363' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (48, N'110115', N'通州区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.363' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (49, N'110116', N'西城区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.373' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (50, N'110117', N'宣武区', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.373' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (51, N'110118', N'延庆县', 33, N'110100', 4, 0, CAST(N'2018-06-25T17:31:12.373' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (52, N'120100', N'天津市', 3, N'120000', 3, 0, CAST(N'2018-06-25T17:31:12.373' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (53, N'120101', N'宝坻区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.383' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (54, N'120102', N'北辰区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.383' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (55, N'120103', N'大港区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.383' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (56, N'120104', N'东丽区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.383' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (57, N'120105', N'汉沽区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.383' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (58, N'120106', N'和平区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.393' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (59, N'120107', N'河北区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.393' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (60, N'120108', N'河东区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.393' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (61, N'120109', N'河西区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.403' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (62, N'120110', N'红桥区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.403' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (63, N'120111', N'蓟县', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.403' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (64, N'120112', N'津南区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.403' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (65, N'120113', N'静海县', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.403' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (66, N'120114', N'南开区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.413' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (67, N'120115', N'宁河县', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.413' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (68, N'120116', N'塘沽区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.413' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (69, N'120117', N'武清区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.413' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (70, N'120118', N'西青区', 52, N'120100', 4, 0, CAST(N'2018-06-25T17:31:12.423' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (71, N'130100', N'保定市', 4, N'130000', 3, 0, CAST(N'2018-06-25T17:31:12.423' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (72, N'130101', N'安国市', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.423' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (73, N'130102', N'安新县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.423' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (74, N'130103', N'北市区', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.433' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (75, N'130104', N'博野县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.433' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (76, N'130105', N'定兴县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.433' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (77, N'130106', N'定州市', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.433' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (78, N'130107', N'阜平县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.433' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (79, N'130108', N'高碑店市', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.443' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (80, N'130109', N'高阳县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.443' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (81, N'130110', N'涞水县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.443' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (82, N'130111', N'涞源县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.443' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (83, N'130112', N'蠡县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.453' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (84, N'130113', N'满城县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.453' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (85, N'130114', N'南市区', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.453' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (86, N'130115', N'清苑县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.453' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (87, N'130116', N'曲阳县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.463' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (88, N'130117', N'容城县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.463' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (89, N'130118', N'顺平县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.463' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (90, N'130119', N'唐县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.463' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (91, N'130120', N'望都县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.463' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (92, N'130121', N'新市区', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.473' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (93, N'130122', N'雄县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.473' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (94, N'130123', N'徐水县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.473' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (95, N'130124', N'易县', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.473' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (96, N'130125', N'涿州市', 71, N'130100', 4, 0, CAST(N'2018-06-25T17:31:12.483' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (97, N'130200', N'沧州市', 4, N'130000', 3, 0, CAST(N'2018-06-25T17:31:12.483' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (98, N'130201', N'泊头市', 97, N'130200', 4, 0, CAST(N'2018-06-25T17:31:12.483' AS DateTime))
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (99, N'130202', N'沧县', 97, N'130200', 4, 0, CAST(N'2018-06-25T17:31:12.483' AS DateTime))
GO
INSERT [dbo].[Area] ([Id], [AreaCode], [AreaName], [ParentId], [ParentCode], [Level], [Sort], [CreatedOn]) VALUES (100, N'130203', N'东光县', 97, N'130200', 4, 0, CAST(N'2018-06-25T17:31:12.483' AS DateTime))

CTE递归查询语句如下：

;WITH T AS (
    SELECT * ,1 AS ALevel FROM Area AS A WHERE A.ParentId=0

    UNION ALL
    SELECT A.*,(T.ALevel + 1) AS ALevel
    FROM Area AS A INNER JOIN T ON A.ParentId=T.Id
    WHERE A.ParentId!=0
)

SELECT * FROM T ORDER BY T.Id

版权声明：本作品系原创，版权归码友网所有，如未经许可，禁止任何形式转载，违者必究。

SQL SQL Server 数据库

上一篇: [LINQ].NET/C#应用程序编程开发中如何使用Linq将一个集合转换成值为集合的字典？

下一篇: [LINQ].NET/C#应用程序编程开发中如何将多表内连接的SQL转换成对应的LINQ查询？

本文永久链接：码友网 » [SQL]SQL Server数据库使用CTE递归查询树形结构的层级深度实现语句

分享扩散：
他们还在浏览...

发表评论

登录用户才能发表评论， 请 登 录 或者 注册