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

Question

是否可以基于层次结构/ cte创建视图？

我看过一个有关如何基于链接递归查询生成结果集的示例。
我已经附上了ddl和声明。

谢谢你，
埃尔默

CREATE 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 all
select 3, 'LFG',null, '1990-01-01','9999-12-31' union all
select 4, 'Elmer',null, '1990-01-01','9999-12-31' union all
select 5, 'Jon',null, '1990-01-01','9999-12-31' union all
select 6, 'Anne',null, '1990-01-01','9999-12-31' union all
select 7, 'Teddy',null, '1990-01-01','9999-12-31' union all
select 8, 'Alex',null , '1990-01-01','9999-12-31'union all
select 9, 'Jeff',null, '1990-01-01','9999-12-31'
update XHR_PERSON set first_name = 'A'

insert into XHR_EMPLOYMENT
select 1, '111',null, '1990-01-01','9999-12-31' UNION ALL 
select 2, '222',1, '1990-01-01','9999-12-31' union all
select 3, '333',1, '1990-01-01','9999-12-31' union all
select 4, '444',2, '1990-01-01','9999-12-31' union all
select 5, '555',2, '1990-01-01','9999-12-31' union all
select 6, '666',4, '1990-01-01','9999-12-31' union all
select 7, '777',3, '1990-01-01','9999-12-31' union all
select 8, '888',3, '1990-01-01','9999-12-31' union all
select 9, '999',8, '1990-01-01','9999-12-31'


CREATE VIEW dbo.HR_DIRECTREPORTSV as 
WITH 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 Level1
FROM 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_date
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
--AND   hrpx.person_id = 1
UNION ALL
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
        ,Level1+1
FROM 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 hrp 
WHERE hre.person_id = hrp.person_id 
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date)

Answer 1

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

select * from xxDirectReports

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

这是一个完整的工作示例，其中选择了视图的最终输出，以及一些其他GO语句，以允许这些语句在SQL Server Manahtml" target="_blank">gement
Studio中一次执行即可运行：

if not OBJECT_ID('XHR_PERSON', 'Table') is null drop table XHR_PERSON
if not OBJECT_ID('XHR_EMPLOYMENT', 'Table') is null drop table XHR_EMPLOYMENT
if not OBJECT_ID('HR_DIRECTREPORTSV', 'View') is null drop view HR_DIRECTREPORTSV

CREATE 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 all
select 3, 'LFG',null, '1990-01-01','9999-12-31' union all
select 4, 'Elmer',null, '1990-01-01','9999-12-31' union all
select 5, 'Jon',null, '1990-01-01','9999-12-31' union all
select 6, 'Anne',null, '1990-01-01','9999-12-31' union all
select 7, 'Teddy',null, '1990-01-01','9999-12-31' union all
select 8, 'Alex',null , '1990-01-01','9999-12-31'union all
select 9, 'Jeff',null, '1990-01-01','9999-12-31'
update XHR_PERSON set first_name = 'A'

insert into XHR_EMPLOYMENT
select 1, '111',null, '1990-01-01','9999-12-31' UNION ALL 
select 2, '222',1, '1990-01-01','9999-12-31' union all
select 3, '333',1, '1990-01-01','9999-12-31' union all
select 4, '444',2, '1990-01-01','9999-12-31' union all
select 5, '555',2, '1990-01-01','9999-12-31' union all
select 6, '666',4, '1990-01-01','9999-12-31' union all
select 7, '777',3, '1990-01-01','9999-12-31' union all
select 8, '888',3, '1990-01-01','9999-12-31' union all
select 9, '999',8, '1990-01-01','9999-12-31';

GO
CREATE VIEW dbo.HR_DIRECTREPORTSV as
WITH 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 Level1
FROM 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_date
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
--AND   hrpx.person_id = 1
UNION ALL
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
        ,Level1+1
FROM 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 hrp

WHERE hre.person_id = hrp.person_id 
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date
AND   GETDATE() between hrp.effective_start_date and hrp.effective_end_date)
select * from xxDirectReports;

GO
select * 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_DIRECTREPORTSV
where Path = '3' or Path like '3.%'

结果如下：

Path   Supervisor_id   Person_id   Employee_number   Employee_name   Supervisor_Empno   Supervisor_Name   Level1
3   1   3   333 LFG, A  111 SMY, A  0
3.7 3   7   777 Teddy, A    333 LFG, A  1
3.8 3   8   888 Alex, A 333 LFG, A  1
3.8.9   8   9   999 Jeff, A 888 Alex, A 2