更新时间:2023-02-05 23:42:31
我尚未测试您的代码,只是试图帮助您了解它在注释中的工作方式;
I haven't tested your code, just tried to help you understand how it operates in comment;
WITH
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>
-- In a rCTE, this block is called an [Anchor]
-- The query finds all root nodes as described by WHERE ManagerID IS NULL
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID IS NULL
-->>>>>>>>>>Block 1>>>>>>>>>>>>>>>>>
UNION ALL
-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>
-- This is the recursive expression of the rCTE
-- On the first "execution" it will query data in [Employees],
-- relative to the [Anchor] above.
-- This will produce a resultset, we will call it R{1} and it is JOINed to [Employees]
-- as defined by the hierarchy
-- Subsequent "executions" of this block will reference R{n-1}
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
r.EmpLevel + 1
FROM Employees e
INNER JOIN cteReports r
ON e.ManagerID = r.EmpID
-->>>>>>>>>>Block 2>>>>>>>>>>>>>>>>>
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID
我可以想到的一个递归 CTE
的最简单示例是;
The simplest example of a recursive CTE
I can think of to illustrate its operation is;
;WITH Numbers AS
(
SELECT n = 1
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n+1 <= 10
)
SELECT n
FROM Numbers
Q 1)N的值如何递增。如果每次都将值分配给N,则可以递增N值,但是只有在第一次初始化N值时才。
A1:
在这种情况下, N
不是变量。 N
是别名。等效于 SELECT 1 AS N
。这是个人喜好的语法。 T-SQL
中的 CTE
中的列别名有两种主要方法。我在 Excel
中包含了一个简单的 CTE
的类似物,以尝试以一种更熟悉的方式说明正在发生的事情
A1:
In this case, N
is not a variable. N
is an alias. It is the equivalent of SELECT 1 AS N
. It is a syntax of personal preference. There are 2 main methods of aliasing columns in a CTE
in T-SQL
. I've included the analog of a simple CTE
in Excel
to try and illustrate in a more familiar way what is happening.
-- Outside
;WITH CTE (MyColName) AS
(
SELECT 1
)
-- Inside
;WITH CTE AS
(
SELECT 1 AS MyColName
-- Or
SELECT MyColName = 1
-- Etc...
)
问题2)现在关于CTE和员工关系递归
当我添加两个经理,并在第二个经理下添加了几个员工之后,问题就开始了。
i要显示第一位经理详细信息,而在接下来的行中,只有那些员工详细信息才会出现在该经理的下属中
A2:
此代码是否回答您的问题?
Does this code answer your question?
--------------------------------------------
-- Synthesise table with non-recursive CTE
--------------------------------------------
;WITH Employee (ID, Name, MgrID) AS
(
SELECT 1, 'Keith', NULL UNION ALL
SELECT 2, 'Josh', 1 UNION ALL
SELECT 3, 'Robin', 1 UNION ALL
SELECT 4, 'Raja', 2 UNION ALL
SELECT 5, 'Tridip', NULL UNION ALL
SELECT 6, 'Arijit', 5 UNION ALL
SELECT 7, 'Amit', 5 UNION ALL
SELECT 8, 'Dev', 6
)
--------------------------------------------
-- Recursive CTE - Chained to the above CTE
--------------------------------------------
,Hierarchy AS
(
-- Anchor
SELECT ID
,Name
,MgrID
,nLevel = 1
,Family = ROW_NUMBER() OVER (ORDER BY Name)
FROM Employee
WHERE MgrID IS NULL
UNION ALL
-- Recursive query
SELECT E.ID
,E.Name
,E.MgrID
,H.nLevel+1
,Family
FROM Employee E
JOIN Hierarchy H ON E.MgrID = H.ID
)
SELECT *
FROM Hierarchy
ORDER BY Family, nLevel
SELECT ID,space(nLevel+
(CASE WHEN nLevel > 1 THEN nLevel ELSE 0 END)
)+Name
FROM Hierarchy
ORDER BY Family, nLevel