创建基于层次结构 CTE使用的视图

创建基于层次结构/ CTE使用的视图

后

WITH

已被指定（超出右括号），您需要选择从CTE的所有值：

select * from xxDirectReports

这是构成视图的实际选择查询。

这是一个完整的工作示例，其中选择了视图的最终输出，以及一些其他

GO

语句，以允许这些语句在SQL Server Management
Studio中一次执行即可运行：

if not OBJECT_ID('XHR_PERSON', 'Table') is null drop table XHR_PERSonif not OBJECT_ID('XHR_EMPLOYMENT', 'Table') is null drop table XHR_EMPLOYMENTif not OBJECT_ID('HR_DIRECTREPORTSV', 'View') is null drop view HR_DIRECTREPORTSVCREATE TABLE [dbo].[XHR_PERSON]([PERSON_ID] [bigint] NOT NULL,[LAST_NAME] [varchar](100) NOT NULL,[FIRST_NAME] [varchar](100),EFFECTIVE_START_DATE Date,EFFECTIVE_END_DATE Date)CREATE TABLE [dbo].[XHR_EMPLOYMENT]([PERSON_ID] [bigint] NOT NULL,[EMPLOYEE_NUMBER] [varchar](100) NULL,[SUPERVISOR_ID] [bigint] NULL,EFFECTIVE_START_DATE Date,EFFECTIVE_END_DATE Date)insert into XHR_PERSON select 1, 'SMY', null, '1990-01-01','9999-12-31' UNIOn ALL select 2, 'JSB',null, '1990-01-01','9999-12-31' union allselect 3, 'LFG',null, '1990-01-01','9999-12-31' union allselect 4, 'Elmer',null, '1990-01-01','9999-12-31' union allselect 5, 'Jon',null, '1990-01-01','9999-12-31' union allselect 6, 'Anne',null, '1990-01-01','9999-12-31' union allselect 7, 'Teddy',null, '1990-01-01','9999-12-31' union allselect 8, 'Alex',null , '1990-01-01','9999-12-31'union allselect 9, 'Jeff',null, '1990-01-01','9999-12-31'update XHR_PERSON set first_name = 'A'insert into XHR_EMPLOYMENTselect 1, '111',null, '1990-01-01','9999-12-31' UNIOn ALL select 2, '222',1, '1990-01-01','9999-12-31' union allselect 3, '333',1, '1990-01-01','9999-12-31' union allselect 4, '444',2, '1990-01-01','9999-12-31' union allselect 5, '555',2, '1990-01-01','9999-12-31' union allselect 6, '666',4, '1990-01-01','9999-12-31' union allselect 7, '777',3, '1990-01-01','9999-12-31' union allselect 8, '888',3, '1990-01-01','9999-12-31' union allselect 9, '999',8, '1990-01-01','9999-12-31';GOCREATE VIEW dbo.HR_DIRECTREPORTSV asWITH xxDirectReports (Supervisor_id, Person_id, Employee_number, Employee_name, Supervisor_Empno, Supervisor_Name, Level1)AS(SELECT hre.Supervisor_id    ,hre.Person_id    ,hre.Employee_number    ,hrp.last_name+', '+hrp.first_name  Employee_Name    ,hrpx.employee_number       Supervisor_Empno    ,hrpx.fullname          Supervisor_Name    ,0 AS Level1FROM dbo.xhr_employment AS hre left join (select hrp1.person_id,hre1.employee_number        ,(hrp1.last_name+', '+hrp1.first_name) as fullname          from dbo.xHR_PERSON hrp1    ,dbo.xhr_employment hre1          where hrp1.person_id = hre1.person_id          AND   getdate() between hrp1.effective_start_date      and    hrp1.effective_end_date       ) hrpx on hre.supervisor_id = hrpx.person_id    ,dbo.xHR_PERSON     AS hrp WHERe hre.person_id = hrp.person_id AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_dateAND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date--AND   hrpx.person_id = 1UNIOn ALLSELECt   hre.Supervisor_id        ,hre.Person_id        ,hre.Employee_number        ,hrp.last_name+', '+hrp.first_name  Employee_Name        ,hrpx.employee_number    Supervisor_Empno        ,hrpx.fullnameSupervisor_Name        ,Level1+1FROM dbo.xhr_employment AS hre inner join (select hrp1.person_id        ,hre1.employee_number    ,(hrp1.last_name+', '+hrp1.first_name) as fullname          from dbo.xHR_PERSON hrp1    ,dbo.xhr_employment hre1          where hrp1.person_id = hre1.person_id          AND   getdate() between hrp1.effective_start_date      and    hrp1.effective_end_date       ) hrpx on hre.supervisor_id = hrpx.person_id          INNER JOIN xxDirectReports AS xx ON hre.Supervisor_id = xx.Person_id    ,dbo.xHR_PERSON     AS hrpWHERe hre.person_id = hrp.person_id AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_dateAND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date)select * from xxDirectReports;GOselect * from HR_DIRECTREPORTSV;

更新：

另外，是否可以基于样本数据LFG而不是SMY来获取以开头的记录？

是的！尽管方法各不相同。 我建议在CTE的锚定语句中包含一个变量，而不是使用视图，而是将其创建为表值函数
。但是，一旦假定采用分层形式，就可以采用多种不同的方法来选择分层数据。方法之一是 Hierarchical Path 。

要添加此行为，请

Path

在

WITH

子句中添加列名，然后首先在anchor语句中添加以下内容：

convert(nvarchar(256), RTRIm(convert(nvarchar(12), hre.PERSON_ID))) Path

其次，在递归语句中：

convert(nvarchar(256), rtrim(Path) + '.' + RTRIm(convert(nvarchar(12), hre.PERSON_ID))) Path

然后，要选择层次结构根（LFG）和所有下属，只需将从视图中选择的查询修改为此：

select * from HR_DIRECTREPORTSVwhere Path = '3' or Path like '3.%'

结果如下：

Path   Supervisor_id   Person_id   Employee_number   Employee_name   Supervisor_Empno   Supervisor_Name   Level13   1   3   333 LFG, A  111 SMY, A  03.7 3   7   777 Teddy, A    333 LFG, A  13.8 3   8   888 Alex, A 333 LFG, A  13.8.9   8   9   999 Jeff, A 888 Alex, A 2