时间戳

首页 > 解决方案 > SQL Server 中的自联接

sql - SQL Server 中的自联接

问题描述

我在 SQL Server 中有两个表：

Employee (Parent):

EMPID | EMPNAME | ROLE      | DEPTID
1     | John    | Manager   | M1
2     | George  | Employee  | E1
3     | Alex    | Intern    | I1
4     | Bob     | Employee  | E2

REL (It holds Emp to Emp relationship):

FROM_EID | TO_EID
1        | 2
2        | 3

预期输出：

RELATED_ID
M1-E1-I1

我正在使用以下查询来获取 EMPID 1、2 和 3 的详细信息，以便稍后连接 DEPTID：

select * from REL rel1, REL rel2, REL rel3
where rel1.FROM_EID = rel2.TO_EID 
and rel2.FROM_EID  = rel3.TO_EID;

但我没有在这里获得 EMPID #1 的详细信息。我怎样才能做到这一点？

标签： sqlsql-serverjoin

解决方案

干得好：

with 
x as (
  select
    empid as startid, 0 as level, 
    empid, cast(concat('', deptid) as varchar(255)) as chain
  from employee where empid = 1
union all
  select
    x.startid, x.level + 1, 
    e.empid, cast(concat(chain, '-', e.deptid) as varchar(255))
  from x
  join rel r on r.from_eid = x.empid
  join employee e on e.empid = r.to_eid
),
y as (
  select startid, max(level) as max_level from x group by startid
)
select x.chain
from x
join y on x.startid = y.startid and x.level = y.max_level

结果：

chain
--------
M1-E1-I1

作为参考，我使用的数据脚本是：

create table employee (
  empid int,
  empname varchar(10),
  role varchar(10),
  deptid varchar(10)
);  

create table rel (
  from_eid int,
  to_eid int
);

insert into employee (empid, empname, role, deptid) values 
  (1, 'John', 'Manager', 'M1'), (2, 'George', 'Employee', 'E1'),
  (3, 'Alex', 'Intern', 'I1'),  (4, 'Bob', 'Employee', 'E2');

insert into rel (from_eid, to_eid) values
  (1, 2), (2, 3);

推荐阅读