找到在自引用表中賺兩倍的員工的第一個父母（經理）？

範例您有下表

CREATE TABLE emp (id,name,salary,manager)
AS
 VALUES
   ( 1  , 'james' ,  10000 , null ),
   ( 2  , 'alex'  ,   5000 , 1 ),
   ( 3  , 'Alice' ,   4500 , 1 ),
   ( 4  , 'Jone'  ,   3000 , 3 ),
   ( 5  , 'Omar'  ,   2200 , 2 );

任何擔任您經理的經理的人也是您的間接經理，因此 Omar 將 Alex 和 James 作為間接經理。我需要一個查詢，讓我成為第一個薪水是員工兩倍或更多的間接經理。

所以結果應該是：

ID | Manager
1  | null
2  | 1
3  | 1
4  | 1
5  | 2

這是您可以嘗試的查詢。當然可以簡化，但我認為這是一個好的開始。

關鍵是遞歸地遍歷所有managers鏈保留工資資訊，以便以後比較。

with recursive t1 as ( -- Traverse emps all the way up in the manager chain
 select id, name, salary, manager, salary indsalary, manager indirect, 1 as level
 from emp 
 union all
   select t1.id, t1.name, t1.salary, e.manager, e.salary, t1.manager, t1.level + 1
   from emp e, t1 
   where t1.manager = e.id
)
, t2 as ( -- Check salary condition
 select *
 from t1
 where  indsalary >= 2 * salary
)
, t3 as ( -- get the row numbers 
 select t2.*, row_number() over (partition by id order by level) rn  
 from t2
)
, t4 as ( -- retain only first row per emp
 select * from t3
 where rn = 1
)
-- Add emps that has no manager or don't fullfill the salary condition
select e.id, t4.indirect manager
from emp e
left join t4
 on (e.id = t4.id);

結果：

id | manager 
----+---------
 1 |  [null]
 2 |       1
 3 |       1
 4 |       1
 5 |       2
(5 rows)

使用遞歸函式和 LATERAL 連接

Next 函式返回給定雇主 ID 的最後一個間接經理。

CREATE OR REPLACE FUNCTION fnManager(pid int)
  RETURNS SETOF employees AS
$BD$
DECLARE
  emp_id int;
  manager_id int;
BEGIN
  SELECT id, manager FROM employees WHERE id = $1
  INTO emp_id, manager_id;

  WHILE manager_id IS NOT NULL LOOP
      SELECT id, manager FROM employees WHERE id = manager_id
      INTO emp_id, manager_id;
  END LOOP;

  RETURN QUERY SELECT * FROM employees WHERE id = emp_id;
  RETURN;
END
$BD$
LANGUAGE 'plpgsql';

然後，您可以將此函式與 LATERAL 聯接一起使用，以獲取每個員工的最後一個間接經理，按薪水過濾。

select emp.id, emp.name, emp.salary, x.id as manager_id , x.name as manager_name, x.salary as manager_salary
from employees emp
join lateral fnManager(emp.id) x ON x.salary >= (emp.salary * 2)
;

編號 | 姓名 | 工資 | manager_id | 經理姓名 | manager_salary
-: | :---- | -----: | ---------: | :----------- | -------------:
 2 | 亞歷克斯 | 5000 | 1 | 詹姆斯 | 10000
 3 | 愛麗絲 | 4500 | 1 | 詹姆斯 | 10000
 4 | 約翰 | 3000 | 1 | 詹姆斯 | 10000
 5 | 奧馬爾 | 2200 | 1 | 詹姆斯 | 10000

dbfiddle在這裡

引用自：https://dba.stackexchange.com/questions/175774