SQL-每个级别都有记录的递归树层次结构

问题描述

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

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，该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-每个级别都有记录的递归树层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！