逆转“具有递归”语句

奥迪诺夫斯基

我有事。查询以显示在董事层次结构下的任何人：

 WITH RECURSIVE emptree AS (
         SELECT e.win_id,
            e.full_name,
            e.current_sup_name,
            e.sbu,
            e.cost_center,
            e.lob,
            0 AS depth
           FROM app_reports.vw_hiearchy e
          WHERE e.win_id = xxxxxxx AND e.attrition_date IS NULL
        UNION ALL
         SELECT e.win_id,
            e.full_name,
            e.current_sup_name,
            e.sbu,
            e.cost_center,
            e.lob,
            t.depth + 1 AS depth
           FROM app_reports.vw_hiearchy e
             JOIN emptree t ON t.win_id = e.current_sup_win
          WHERE e.attrition_date IS NULL
        )
 SELECT emptree.win_id,
    emptree.full_name,
    emptree.current_sup_name,
    emptree.sbu,
    emptree.cost_center,
    emptree.lob,
    emptree.depth
   FROM emptree;

直到我被要求添加另一列（或更多列）以显示谁是特定主管的主管（在技术上动态添加列（如果可能，从下至上显示所有主管））之前，它都可以正常工作。我不确定是否需要颠倒此过程，以使我真正从下至上获得层次结构并将其显示为current_sup_name_2，current_sup_name3等。但我不确定如何。

在此先感谢您的任何建议。:)

359

只需对现有查询进行少量修改，就可以在一个字段中显示主管的完整层次结构：

WITH RECURSIVE emptree AS (
         SELECT e.win_id,
            e.full_name,
            e.current_sup_name,
            e.sbu,
            e.cost_center,
            e.lob,
            0 AS depth
           FROM app_reports.vw_hiearchy e
          WHERE e.win_id = xxxxxxx AND e.attrition_date IS NULL
        UNION ALL
         SELECT e.win_id,
            e.full_name,
            concat_ws(',', e.current_sup_name, t.current_sup_name) current_sup_name,
            e.sbu,
            e.cost_center,
            e.lob,
            t.depth + 1 AS depth
           FROM app_reports.vw_hiearchy e
             JOIN emptree t ON t.win_id = e.current_sup_win
          WHERE e.attrition_date IS NULL
        )
 SELECT emptree.win_id,
    emptree.full_name,
    emptree.current_sup_name,
    emptree.sbu,
    emptree.cost_center,
    emptree.lob,
    emptree.depth
   FROM emptree;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。