Intereting Posts
Список python в sql-запросе как параметр Запрос в objectdb Scope_identity () в пакетных командах sqlclient Автоматический прирост Oracle SQL PLS-00103 mysql-запрос для динамического преобразования данных строк в столбцы Что означает знак (+) в предложении Oracle SQL WHERE? Как использовать CriteriaQuery SUM для пользовательской операции в некоторых ячейках? Критерии Hibernate Query – вложенное условие Месячный инкрементный запрос Соединение C ++ с Azure SQL или Microsoft SQL Server Количество параметров запроса MySQL соответствует аргументам, переданным для выполнения, но Python повышает «не все преобразованные аргументы», Подсчитайте несоответствие и отсутствие Создание предложений запроса с помощью korma и clojure Адреса, хранящиеся на SQL-сервере, имеют множество небольших изменений (ошибок) Как принудительно применять только один набор полей для не-NULL исключительно среди множества (более 2) наборов?

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