Intereting Posts
Возврат pre-UPDATE Значения столбца с использованием только SQL – версия PostgreSQL Обновить набор данных из sql C # Как передать набор строк из одной функции в другую? проверить создание объектов базы данных, таких как триггеры, процедуры, разрешения Понимание того, как работает JOIN, когда задействованы 3 или более таблиц. Не удается создать хранилище выше 10 ORACLE Как использовать катушку с динамическим местоположением катушки Hibernate SQL Результат запроса Отображение / преобразование объекта Object / Class / Bean Поле отображения из другой таблицы в SQL Сводная таблица SQL с итогом столбцов и строк Триггер для отслеживания изменений в базе данных MySQL Как добавить значение по умолчанию в SQLite? (Как) можно ли число групп результатов запроса по строке / порядку результата в одном запросе? Postgres LEFT JOIN with SUM, отсутствующие записи понимание MySQL Объяснение вывода

Создать представление, основанное на иерархии / используемом CTE

Возможно ли создать представление на основе иерархии / 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) 

После того, как WITH был указан (за закрывающей скобкой), вам нужно выбрать все значения из CTE:

 select * from xxDirectReports 

Это фактический запрос выбора, который представляет собой представление.

Это полный рабочий пример с выбором окончательного вывода представления и некоторыми дополнительными 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; 

Обновить:

Кроме того, возможно ли получить запись, которая начинается с данных выборок, LFG, а не с SMY?

Да! Хотя подход меняется. Я бы порекомендовал иметь переменную в заявлении привязки CTE, и вместо того, чтобы иметь представление, создайте ее как функцию, зависящую от таблицы . Тем не менее, существует несколько разных способов выбора иерархических данных, как только предполагается, что это иерархическая форма … одним из подходов является Иерархический путь .

Чтобы добавить это поведение, добавьте имя столбца Path в предложение WITH и добавьте следующее, во-первых, в оператор привязки:

 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 

Да, вы можете – что-то вроде

 CREATE VIEW [mySchema].vwItem AS WITH cteItem AS ( SELECT item_id AS 'ItemID', item_level AS 'ItemLevel', item_number_type AS 'ItemNumberType', CASE item_level WHEN 1 THEN item_id WHEN 2 THEN item_parent_id WHEN 3 THEN item_grandparent_id ELSE item_id END AS 'ItemParent', FROM [mySchema].[ITEM] ) SELECT child.*, parent.item_id AS 'ParentId', parent.desc AS 'ParentDescription' FROM cteItem child LEFT OUTER JOIN [mySchema].[ITEM] parent ON child.ItemParent = parent.item_id