SQL - 每个级别都有记录的递归树层次结构 [英] SQL - Recursive Tree Hierarchy with Record at Each Level

问题描述

尝试使用 SAS(据我所知不支持 WITH RECURSIVE)在 SQL 中创建经典的层次结构树.

Trying to do a classic hierarchy tree in SQL, using SAS (which does not support WITH RECURSIVE, so far as I know).

这是现有表中的简化数据结构:

Here's simplified data structure in existing table:

|USER_ID|SUPERVISOR_ID|

因此，要构建层次结构，您只需将其递归 x 次即可获取您要查找的数据，其中 SUPERVISOR_ID = USER_ID.在我公司，是 16 级.

So, to build a hierarchy, you just recursively join it x number of times to get data you are looking for, where SUPERVISOR_ID = USER_ID. In my company, it is 16 levels.

当尝试为每个用户终止分支时会出现此问题.例如，假设第 1 级的用户 A 在第 2 级下有用户 B、C、D 和 E.因此，使用递归 LEFT JOIN，您将得到:

This issue comes when trying to get a branch to terminate for each user. For example, let's consider User A at level 1 has Users B,C,D, and E under them, at level 2. Thus, using a recursive LEFT JOIN, you would get:

| -- Level 1 -- | -- Level 2 -- |
     User A          User B
     User A          User C
     User A          User D
     User A          User E

问题是，用户 A 没有自己的终止分支.需要的最终结果是:

Issue being, User A does not have their own terminating branch. End result needed is:

| -- Level 1 -- | -- Level 2 -- |
     User A           NULL         
     User A          User B
     User A          User C
     User A          User D
     User A          User E

我第一个脸红的想法是我可以通过在每个级别创建一个临时表然后对结果完全执行 UNION ALL 来解决这个问题，但是考虑到大小(16 个级别)，这似乎非常低效，我希望我错过了这是一个更清洁的解决方案.

My first blush thought is I can get around this by creating a temp table at each level then performing a UNION ALL on the results altogether, however that seems terribly inefficient given the size (16 levels) and am hoping I'm missing something here that is a cleaner solution.

推荐答案

我不太确定我是否理解这个问题，但如果你想生成每个主管下所有员工的完整列表，那么这是一种方法这样做，假设每个员工都有一个唯一的 ID，它可以出现在用户或主管列中:

I'm not quite sure I understand the question, but if you're trying to generate a full listing of all employees under each supervisor then this is one way of doing it, assuming that each employee has a unique ID, which can appear in either the user or supervisor column:

data employees;
input SUPERVISOR_ID USER_ID;
cards;
1 2
1 3
1 4
2 5
2 6
2 7
7 8
;
run;

proc sql;
  create view distinct_employees as 
  select distinct SUPERVISOR_ID as USER_ID from employees
  union
  select distinct USER_ID from employees;
quit;

data hierarchy;
  if 0 then set employees;
  set distinct_employees;
  if _n_ = 1 then do;
    declare hash h(dataset:'employees');
    rc = h.definekey('USER_ID');
    rc = h.definedata('SUPERVISOR_ID');
    rc = h.definedone();
  end;
  T_USER_ID = USER_ID;
  do while(h.find() = 0);
    USER_ID = T_USER_ID;
    output;
    USER_ID = SUPERVISOR_ID;
  end;
  drop rc T_USER_ID;
run;

proc sort data = hierarchy;
  by SUPERVISOR_ID USER_ID;
run;

这篇关于SQL - 每个级别都有记录的递归树层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！