时间戳

首页 > 解决方案 > 递归 CTE 血统（拥有多个经理的员工）

postgresql - 递归 CTE 血统（拥有多个经理的员工）

问题描述

我在尝试将所有经理输出到员工之上时遇到困难。employees 表没有 manager_id 列，这使得这有点复杂。

层次结构在参考表中定义。每个员工都属于一个部门并被分配一个职位。级别 1 的职位是经理，级别 0 是非经理。

要获取经理的经理，我们获取当前部门的 parent_id 并查找分配有级别 1 职位的员工。

我目前拥有的功能仅返回员工的直接经理（来自非递归术语）。

CREATE OR REPLACE FUNCTION public.get_employee_managers(employees_row employees)
 RETURNS SETOF employees
 LANGUAGE sql
 STABLE
AS $function$
WITH RECURSIVE managers AS (
    SELECT e1.*
    FROM
      employees e1
      JOIN "references" AS employees_row_department ON employees_row_department.id = employees_row.department_id
      JOIN "references" AS e1_department ON e1_department.id = e1.department_id
      JOIN "references" AS e1_position ON e1_position.id = e1.position_id
    WHERE
      e1_department.id = employees_row_department.parent_id AND e1_position.level = 1 AND e1.active = 1 AND e1.is_deleted = false
    OR
      e1_department.id = employees_row.department_id AND e1_position.level = 1 AND e1.active = 1 AND e1.is_deleted = false AND e1.id <> employees_row.id
    UNION
    SELECT m1.*
    FROM
      managers m1
      JOIN "references" AS m1_department ON m1_department.id = m1.department_id
      JOIN "references" AS m1_position ON m1_position.id = m1.position_id
      INNER JOIN employees AS e2 ON (m1_department.parent_id = e2.department_id AND m1_position.level = 1 AND e2.active = 1 AND e2.is_deleted = false)
)
SELECT * FROM managers ORDER BY department_id ASC
$function$

使用下面的递归项给了我与上面相同的结果

SELECT e2.*
    FROM
      employees e2
      JOIN "references" AS e2_department ON e2_department.id = e2.department_id
      JOIN "references" AS e2_position ON e2_position.id = e2.position_id
      INNER JOIN managers m1 ON m1.id = e2.id
      JOIN "references" AS m1_department ON m1_department.id = m1.department_id
    WHERE
      e2_department.id = m1_department.parent_id AND e2_position.level = 1 AND e2.active = 1 AND e2.is_deleted = false

标签： postgresqlhasura

解决方案

更改第二个递归项似乎给了我想要的结果。

      INNER JOIN managers m1 ON m1.id = e2.id

至

      INNER JOIN managers m1 ON m1.id <> e2.id

推荐阅读