如何在SQL中创建一个基于其自身数据集动态赋值的字段

Question

我使用Microsoft SQL Server 2012在SQL中创建了一个查询。

该查询提供员工、他们的主管和职务的列表。

以下是示例数据：

+----------------+----------------+-----------------+
|    Employee    |   Supervisor   |    Job Title    |
+----------------+----------------+-----------------+
| Joe Bloggs     | Joe Bloggs     | Owner           |
| John Doe       | Joe Bloggs     | ESTIMATOR       |
| Jack Nimble    | Joe Bloggs     | ESTIMATOR       |
| Tom Hanks      | John Doe       | Admin           |
| Les Mis        | Tom Hanks      | IT Tech         |
| Bruce Lee      | Tom Hanks      | Mechanic        |
| Norman Gunston | Jack Nimble    | Accountant      |
| Fred Flinstone | John Doe       | Web Designer    |
| Brady North    | Norman Gunston | Receptionist    |
| Chris Pork     | Norman Gunston | Accountant      |
| Tom Cruise     | Tom Hanks      | General Manager |
| Jason Smith    | Jack Nimble    | IT Tech         |
| John Howard    | Tom Hanks      | Trainer         |
+----------------+----------------+-----------------+.

正如您所看到的，员工可以有一个也在同一员工列表中的主管。例如，Tom Hanks有一位名叫John Doe的主管，他是一位名叫Joe Bloggs的主管的员工。

在这种情况下，Joe Bloggs是他自己的主管，因为他拥有公司。

这是产生上述结果的SQL代码：

select HRRMName.FullName as 'Employee', HRRM.udSupervisor1 as 'Supervisor', HRPC.JobTitle from HRRM
LEFT JOIN HRPC
ON HRPC.HRCo = HRRM.HRCo and HRPC.PositionCode = HRRM.PositionCode
LEFT JOIN HRRMName ON HRRMName.HRCo = HRRM.HRCo and HRRMName.HRRef = HRRM.HRRef
where HRRM.HRCo = 1 and HRRM.ActiveYN = 'Y'

我的目标是使用SSRS来开发公司组织结构图，但是，首先我需要通过添加一个名为"级别"的新字段来修改我的数据集（查询）。

"级别"字段将是一个整数。它将从公司所有者的1开始，然后是他手下的2名员工，然后是他们手下的3名员工，依此类推

下面是一个新结果的例子：

+----------------+----------------+-----------------+-------+
|    Employee    |   Supervisor   |    Job Title    | Level |
+----------------+----------------+-----------------+-------+
| Joe Bloggs     | Joe Bloggs     | Owner           |     1 |
| John Doe       | Joe Bloggs     | ESTIMATOR       |     2 |
| Jack Nimble    | Joe Bloggs     | ESTIMATOR       |     2 |
| Tom Hanks      | John Doe       | Admin           |     3 |
| Les Mis        | Tom Hanks      | IT Tech         |     4 |
| Bruce Lee      | Tom Hanks      | Mechanic        |     4 |
| Norman Gunston | Jack Nimble    | Accountant      |     3 |
| Fred Flinstone | John Doe       | Web Designer    |     3 |
| Brady North    | Norman Gunston | Receptionist    |     4 |
| Chris Pork     | Norman Gunston | Accountant      |     4 |
| Tom Cruise     | Tom Hanks      | General Manager |     4 |
| Jason Smith    | Jack Nimble    | IT Tech         |     3 |
| John Howard    | Tom Hanks      | Trainer         |     4 |
+----------------+----------------+-----------------+-------+

如何动态地产生这个值？

我在想这个Psuedo代码：

Declare @level int;
Set @level = 1;
IF table.employee = table.supervisor then table.level = @Level
@level ++
IF table.supervisor = table.employee where level = (@level - 1)  then table.level = @Level
@level ++

我正在寻求如何获得新的动态行的建议。

Answer 1

您可以使用递归CTE来完成此操作。我认为以下内容符合您的要求：

with t as (<your query here>),
     cte as (
      select t.employee, t.supervisor, t.jobtitle, 1 as level, t.
      from t
      where employee = supervisor
      union all
      select t.employee, t.supervisor, t.jobtitle, level + 1
      from cte join
           t
           on t.supervisor = cte.employee and t.supervisor <> t.employee
     )
 select employee, supervisor, jobtitle
 from cte;