自联接查询

Question

manager_id<1>1002插孔

emp_id	emp	级别	经理
100	sam		sam
200	插孔	100	sam
300	jill	2	100	sam
400	claire	3	200
500	簧片	300	jill
	井架		4	400	claire
	票据		4	500	里德

您似乎在寻找一个递归查询。一个继续加入下一个级别，直到没有更多级别可加入。

这可以用来获取所有员工的经理，每个经理都作为一个新行。

然后，您需要将这些行转换为列。但是，请注意，SQL是静态和强类型的，这意味着如果你想要一个透视视图，你必须提前选择要有多少列。

例如。。。

WITH
recurse_upwards AS
(
SELECT
emp.emp_id,
emp.emp          AS emp_name,
0                AS level,
mgr.emp_id       AS manager_id,
mgr.emp          AS manager_name,
mgr.manager_id   AS next_manager_id
FROM
example   AS emp
LEFT JOIN
example   AS mgr
ON mgr.emp_id = emp.manager_id
UNION ALL
SELECT
emp.emp_id,
emp.emp_name,
emp.level + 1,
mgr.emp_id,
mgr.emp,
mgr.manager_id
FROM
recurse_upwards   AS emp
INNER JOIN
example           AS mgr
ON mgr.emp_id = emp.next_manager_id
)
SELECT
emp_id,
emp_name,
MAX(CASE WHEN level = 0 THEN manager_id   END)  AS manager_id,
MAX(CASE WHEN level = 0 THEN manager_name END)  AS manager_name,
MAX(CASE WHEN level = 1 THEN manager_name END)  AS manager_1_name,
MAX(CASE WHEN level = 2 THEN manager_name END)  AS manager_2_name,
MAX(CASE WHEN level = 3 THEN manager_name END)  AS manager_3_name,
MAX(CASE WHEN level = 4 THEN manager_name END)  AS manager_4_name
FROM
recurse_upwards
GROUP BY
emp_id,
emp_name
ORDER BY
emp_id

演示：https://dbfiddle.uk/Tj7rZ5bT