sql - 基于 CTE 的层次结构/使用创建 View

Question

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

我看过一个关于如何根据链接 recursive query 生成结果集的示例.
我附上了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

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

这是完整的工作示例，选择了 View 的最终输出，以及一些额外的 GO 语句以允许语句在 SQL Server Management 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;

更新:

Also, is it possible to fetch the record that starts with , based on sample data, LFG and not with SMY?

是的!虽然方法不同。 我建议在 CTE 的锚定语句中有一个变量，而不是有一个 View ，而是将其创建为一个表值函数。但是，一旦采用分层形式，就有许多不同的方法来选择分层数据……其中一种方法是分层路径。

要添加此行为，请将 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) 和所有下级，只需将从 View 中选择的查询修改为:

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