sql - 选择父子记录

Question

我如何选择一家公司及其子公司的所有员工？

使用 SQL Server 2008

员工

Id | Name | CompanyId  

公司

Id | Name | ParentCompanyId  

例子:

1 微软 0

2 微软印度 1

3 微软西类牙 1

我有以下查询，它只提供来自微软的员工，而不是来自微软印度和西类牙的员工。

SELECT Id, Name FROM Employee WHERE CompanyId=1

我不擅长 SQL。帮我解决这个问题。

Answer 1

使用 CTE 构建公司层次结构，然后将其连接回员工表:

with CompanyHierarchy as
(
  select Id
  from Company
  where Id = 1
  union all
  select c.Id
  from Company c
    inner join CompanyHierarchy ch on c.ParentCompanyId = ch.Id
)
select e.*
from CompanyHierarchy ch
  inner join Employees e on ch.Id = e.CompanyId

SQL Fiddle with demo .

您也可以用 CompanyId 代替如果要参数化语句，请将变量添加到 CTE 的 anchor 部分:

with CompanyHierarchy as
(
  select Id
  from Company
  where Id = @CompanyId
  union all
  select c.Id
  from Company c
    inner join CompanyHierarchy ch on c.ParentCompanyId = ch.Id
)
select e.*
from CompanyHierarchy ch
  inner join Employees e on ch.Id = e.CompanyId

SQL Fiddle with demo ，现在添加了层次结构级别。