WITH
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
r.EmpLevel + 1
FROM Employees e
INNER JOIN cteReports r
ON e.ManagerID = r.EmpID
)
SELECT
FirstName + ' ' + LastName AS FullName,
EmpLevel,
(SELECT FirstName + ' ' + LastName FROM Employees
WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID
这里我发布有关输出显示方式的信息:
我只需要知道它是如何先显示经理,然后是其下属的循环。
我猜第一个sql语句仅触发一次,并返回所有员工ID。
第二个查询反复触发,请使用当前经理ID查询存在雇员的数据库。
请解释sql语句如何在内部循环中执行,并告诉我sql的执行顺序。谢谢。
我的第二阶段问题
;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值时。
Q 2)CTE和员工关系递归:
我添加两个经理并在第二个经理下添加更多雇员的那一刻开始出现问题。
我想显示第一个经理的详细信息,而在下一行中仅显示与之相关的那些雇员的详细信息
假设
ID Name MgrID Level
--- ---- ------ -----
1 Keith NULL 1
2 Josh 1 2
3 Robin 1 2
4 Raja 2 3
5 Tridip NULL 1
6 Arijit 5 2
7 Amit 5 2
8 Dev 6 3
我想用CTE表达式来显示结果。请告诉我在此处给出的sql中进行哪些修改,以拉动经理与员工的关系。谢谢。
我希望输出像这样:
ID Name MgrID nLevel Family
----------- ------ ----------- ----------- --------------------
1 Keith NULL 1 1
3 Robin 1 2 1
2 Josh 1 2 1
4 Raja 2 3 1
5 Tridip NULL 1 2
7 Amit 5 2 2
6 Arijit 5 2 2
8 Dev 6 3 2
这可能吗??
#1 楼
我尚未测试您的代码,只是试图帮助您了解它在注释中的工作方式;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
示例它的操作是; Q4312078q
Q 1)N的值如何递增。如果将值每次分配给N,则可以递增N值,但是只有在第一次初始化N值时。
A1:
在这种情况下,N
不是变量。 N
是别名。等效于SELECT 1 AS N
。这是个人喜好的语法。在CTE
中的T-SQL
中有2种别名化列的主要方法。我在CTE
中加入了一个简单的Excel
的类似物,以尝试以更熟悉的方式说明正在发生的事情。;WITH Numbers AS
(
SELECT n = 1
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n+1 <= 10
)
SELECT n
FROM Numbers
问题2)现在这里是关于CTE和员工关系递归的问题。
当我添加两名经理并在第二名经理下添加几名员工然后问题开始的时候。仅将那些员工详细信息排到该经理的下属。
A2:
此代码是否回答您的问题?
-- Outside
;WITH CTE (MyColName) AS
(
SELECT 1
)
-- Inside
;WITH CTE AS
(
SELECT 1 AS MyColName
-- Or
SELECT MyColName = 1
-- Etc...
)
另一个具有树结构的sql
--------------------------------------------
-- 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
#2 楼
想要概述一个与已经正确的答案平行的简短语义。用“简单”的术语,递归CTE可以在语义上定义为以下部分:
1: CTE查询。
2:(1)中的CTE上具有UNION ALL(或UNION或EXCEPT或INTERSECT)的递归CTE查询,因此相应地返回最终结果。
3:转角/终止条件。递归查询没有返回更多的行/元组时,默认情况下为默认值。
使图片清晰的简短示例:
;WITH SupplierChain_CTE(supplier_id, supplier_name, supplies_to, level)
AS
(
SELECT S.supplier_id, S.supplier_name, S.supplies_to, 0 as level
FROM Supplier S
WHERE supplies_to = -1 -- Return the roots where a supplier supplies to no other supplier directly
UNION ALL
-- The recursive CTE query on the SupplierChain_CTE
SELECT S.supplier_id, S.supplier_name, S.supplies_to, level + 1
FROM Supplier S
INNER JOIN SupplierChain_CTE SC
ON S.supplies_to = SC.supplier_id
)
-- Use the CTE to get all suppliers in a supply chain with levels
SELECT * FROM SupplierChain_CTE
解释:
第一个CTE查询返回不直接向任何其他供应商供应(-1)的基础供应商(如叶子)
第一次迭代中的递归查询得到向ANCHOR返回的供应商供应的所有供应商。
此过程一直持续到条件返回元组为止。
UNION ALL返回总递归调用中的所有元组。
这里可以找到另一个很好的例子。例如:elementId = elementParentId ..您明白了。
#3 楼
执行过程确实与递归CTE混淆,我在https://technet.microsoft.com/zh-cn/library/ms186243(v=sql.105).aspx和CTE执行过程的摘要中找到了最佳答案如下。递归执行的语义如下:
将CTE表达式拆分为锚点和递归成员。
运行锚成员创建第一个调用或基本结果集(T0)。
以Ti作为输入并以Ti + 1作为输出运行递归成员。
重复步骤3直到出现返回空集。
返回结果集。这是T0到Tn的UNION ALL。
#4 楼
--DROP TABLE #Employee
CREATE TABLE #Employee(EmpId BIGINT IDENTITY,EmpName VARCHAR(25),Designation VARCHAR(25),ManagerID BIGINT)
INSERT INTO #Employee VALUES('M11M','Manager',NULL)
INSERT INTO #Employee VALUES('P11P','Manager',NULL)
INSERT INTO #Employee VALUES('AA','Clerk',1)
INSERT INTO #Employee VALUES('AB','Assistant',1)
INSERT INTO #Employee VALUES('ZC','Supervisor',2)
INSERT INTO #Employee VALUES('ZD','Security',2)
SELECT * FROM #Employee (NOLOCK)
;
WITH Emp_CTE
AS
(
SELECT EmpId,EmpName,Designation, ManagerID
,CASE WHEN ManagerID IS NULL THEN EmpId ELSE ManagerID END ManagerID_N
FROM #Employee
)
select EmpId,EmpName,Designation, ManagerID
FROM Emp_CTE
order BY ManagerID_N, EmpId
评论
这是仅代码的答案,甚至没有答案,因为其中没有递归CTE。
–德拉戈莫克
17年6月11日在15:49
评论
CTE递归查询不会以我想要的方式返回结果。我想显示第一个经理姓名,然后再次显示其所有下属。显示第二个经理姓名,然后显示其所有下属。我希望输出应该是这种方式。如果可能的话,请更新您的查询。谢谢
–托马斯
2013年1月11日下午13:51
[家庭]列添加。现在检查。
– MarkD
2013年1月11日15:08
在这里,我以我想要显示结果的方式给出输出。请检查并告诉我是否有可能...如果是,则在ur sql中进行必要的修改。感谢您的努力。
–托马斯
2013年1月11日19:03
为什么是“;”在WITH语句之前? “;有”谢谢
–德鲁丁
15年1月21日,16:03
@ SiKni8-链接似乎已死
– MarkD
2015年10月21日在7:30