T-SQL 递归查询示例

最新推荐文章于 2023-01-06 18:16:10 发布

besley

最新推荐文章于 2023-01-06 18:16:10 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库/LINQ 文章标签： sql 数据库

本文链接：https://blog.youkuaiyun.com/besley/article/details/40820367

数据库/LINQ 专栏收录该内容

4 篇文章

订阅专栏

这篇博客介绍了如何在SQL数据库中利用CTE（公共表表达式）进行递归查询，通过创建MyEmployees表并展示查询语句，展示了如何查询员工及其上下级关系，最后以baseEmployeeID和level排序结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 数据库表创建

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
	EmployeeID smallint NOT NULL,
	FirstName nvarchar(30)  NOT NULL,
	LastName  nvarchar(40) NOT NULL,
	Title nvarchar(50) NOT NULL,
	DeptID smallint NOT NULL,
	ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

2.查询语句

SELECT * FROM myemployees

;WITH CTE AS(
SELECT
E.EmployeeID AS baseEmployeeID,
E.EmployeeID,
E.ManagerID,
0 AS level
FROM MyEmployees E

UNION All

SELECT
T.baseEmployeeID,
P.EmployeeID,
P.ManagerID,
T.level + 1
FROM CTE T
INNER JOIN MyEmployees P
ON T.ManagerID = P.EmployeeID
)

SELECT * FROM CTE
--WHERE managerId = 1
ORDER BY CTE.baseEmployeeID, level