我已经在此站点上查看了许多递归CTE示例,并且尝试将其应用于我自己的数据。与大多数示例相比,我似乎有更多的联接,但我认为我能到达那里。我需要帮助的问题是我收到一条错误消息:
声明终止。在语句完成之前,最大递归100已用尽。
我已经运行了CTE的第一条select语句,并且反复运行了第二条select语句(具有不同的实际职位代码),并且该员工有六个级别,因此我看不到为什么会出错。
我的一个怀疑领域是我必须应用标准'relationship_type_id = 0',因为这是'Reports To'关系类型。我曾尝试将其设为左外部联接,但不允许这样做。这意味着我不会使用此查询来获得最高级别的经理(即本身没有经理的人)。
我已经在下面发布了代码,任何帮助将不胜感激。
WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, PositionCode, ReportsTo, HierarchyLevel) AS
(
SELECT
p.employee_id as EmployeeID,
p.last_name as LastName,
p.first_name as FirstName,
pos.position_code as PositionCode,
r.to_position_code as ReportsTo,
1 as HierarchyLevel
FROM
--JOIN: Personal details
dbo.person p
--JOIN: Employment links a person to a post (could have more than one)
INNER JOIN
dbo.employment e ON e.employee_id = p.employee_id
--JOIN: details of the position held
INNER JOIN
dbo.position pos ON pos.position_code = e.position_code
--JOIN: Relationships between the positions, one position reports to another position etc.
-- There are several 'relationship types', we are only interested in relationship_type_id = 0
-- as this is the 'Reports to' relationship code. Others types include 'Managed by' etc.
INNER JOIN
dbo.relationship r ON r.from_position_code = pos.position_code AND r.relationship_type_id = 0
WHERE
--CRITERIA: Use my employee Id as a starting point for testing
p.employee_id = '10076395'
UNION ALL
-- Recursive step
SELECT
p2.employee_id as EmployeeID,
p2.last_name as LastName,
p2.first_name as FirstName,
pos2.position_code as PositionCode,
r2.to_position_code as ReportsTo,
eh.HierarchyLevel + 1 AS HierarchyLevel
FROM
dbo.person p2
INNER JOIN
dbo.employment e2 ON e2.employee_id = p2.employee_id
INNER JOIN
dbo.position pos2 ON pos2.position_code = e2.position_code
INNER JOIN
dbo.relationship r2 ON r2.from_position_code = pos2.position_code AND r2.relationship_type_id = 0
--JOIN: Link this query back to the base query
INNER JOIN
EmployeeHierarchy eh ON r2.from_position_code = eh.PositionCode
)
SELECT *
FROM EmployeeHierarchy
ORDER BY HierarchyLevel, LastName, FirstName
试试看。
我已将复杂的联接移至单独的CTE,以保持递归部分尽可能整洁和简洁。如果没有别的,这种方法应该可以帮助您查明问题。
; WITH employees AS (
SELECT person.employee_id
, person.last_name
, person.first_name
, position.position_code
, relationship.to_position_code
FROM dbo.person
INNER
JOIN dbo.employment
ON employment.employee_id = person.employee_id
INNER
JOIN dbo.position
ON position.position_code = employment.position_code
INNER
JOIN dbo.relationship
ON relationship.from_position_code = position.position_code
AND relationship.relationship_type_id = 0
)
, recursive_bit AS (
SELECT employee_id
, last_name
, first_name
, position_code
, to_position_code
FROM employees
WHERE employee_id = '10076395'
UNION ALL
SELECT employees.employee_id
, employees.last_name
, employees.first_name
, employees.position_code
, employees.to_position_code
FROM recursive_bit
INNER
JOIN employees
ON employees.position_code = recursive_bit.to_position_code
)
SELECT *
FROM recursive_bit
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句