Как спроектировать SQL-рекурсивный запрос? Запрос рекурсивный sql
Sql server - Рекурсивный запрос в MS SQL | GeekBrains
Нид хэлп!Изучаю примеры рекурсивных запросов sql. Чтобы потренироваться с такими запросами, создал таблицу, в которой есть поля id (код записи) и id_parent (код родительской записи). Пример взял с одного сайта:
create table tree_sample ( id integer not null primary key, id_parent integer foreign key references tree_sample (id),nm varchar(31))Таблицу наполнил данными. Для записей, у которых id_parent = NULL, считается, что это запись без родителя - самая верхняя в дереве (их может быть много). Для примера забил туда 2 строки: id = 1 "Книги", id = 2 "Фильмы". Затем забил еще несколько строк, в которых в id_parent = 1 или 2 (каждая с уникальным id и заголовком книги/фильма в поле nm).После этого по тому же примеру из интернета слепил запрос:
with tree (nm, id, level, pathstr) as (select nm, id, 0, cast(nm as varchar) from tree_sample where id_parent is null union all select tree_sample.nm, tree_sample.id, tree.level + 5, cast(tree.nm + tree_sample.nm as varchar) from tree_sample inner join tree on tree.id = tree_sample.id_parent) select id, space( level ) + nm as nm from tree order by pathstrСитуация меняется, если добавить потомков для одного из других потомков (например, к записи с фильмом "Властелин колец" добавить потомков: часть 1, 2, 3). Эти потомки выводятся с правильным отступом (уже в 10 пробелов), но не непосредственно под своим предком, а в случайном месте (ну, или не случайном, зависит от того, что попало в pathstr).На этом я заглох. Не знаю, как слепить запрос так, чтобы ВСЕ потомки оказались непосредственно под своими предками.Кто может подсказать, что нужно еще допилить в запросе?Это вообще реально сделать?
geekbrains.ru
sql - Как создать иерархический рекурсивный запрос MySQL
Для версий MySql, которые не поддерживают Common Table Expressions (до версии 5.7), вы достигнете этого следующим запросом:
select id, name, parent_id from (select * from products order by parent_id, id) products_sorted, (select @pv := '19') initialisation where find_in_set(parent_id, @pv) and length(@pv := concat(@pv, ',', id))Вот скрипка.
Значение, указанное в @pv := '19' должно быть установлено на id родителя, для которого вы хотите выбрать всех потомков.
Это также будет работать, если у родителя есть несколько детей. Однако требуется, чтобы каждая запись удовлетворяла условию parent_id < id, иначе результаты не будут выполнены.
Этот запрос использует специфический синтаксис MySql: переменные назначаются и изменяются во время его выполнения. Некоторые допущения сделаны относительно порядка исполнения:
- Сначала выполняется предложение from. Так вот где @pv получает инициализацию.
- Предложение where оценивается для каждой записи в порядке извлечения from псевдонимов. Таким образом, это условие включает только записи, для которых родительский элемент уже идентифицирован как находящийся в дереве потомков (все потомки основного родителя постепенно добавляются к @pv).
- Условия в этом аргументе where оцениваются по порядку, и оценка прерывается после того, как общий результат определен. Поэтому второе условие должно быть на втором месте, поскольку оно добавляет id в родительский список, и это должно произойти только в том случае, если id передает первое условие. Функция length вызывается только для того, чтобы убедиться, что это условие всегда истинно, даже если строка pv по какой-то причине дает значение фальшивости.
В целом, можно полагать, что эти допущения слишком рискованны, чтобы полагаться на них - для них нет документальной гарантии, и хотя она работает последовательно, порядок оценки может теоретически по-прежнему меняться, когда вы используете этот запрос как представление или подзаголовок, запрос в более крупном запросе.
Также обратите внимание, что для очень больших наборов данных это решение может замедляться, поскольку операция find_in_set не является идеальным способом найти число в списке, но, конечно, не в списке, который достигает размера в том же порядке, что и число возвращенных записей.
Альтернатива 1: WITH RECURSIVE, CONNECT BY
Все больше и больше баз данных реализуют стандарт ISO: 1999 ISO с синтаксисом WITH [RECURSIVE] для рекурсивных запросов (например, Postgres 8. 4+, SQL Server 2005+, DB2, Oracle 11gR2+, SQLite 3.8. 4+, Firebird 2. 1+, h3, HyperSQL 2.1. 0+, Teradata, MariaDB 10.2. 2+). А с версии 8.0 также поддерживает MySql. С этим синтаксисом запрос выглядит так:
with recursive cte (id, name, parent_id) as ( select id, name, parent_id from products where parent_id = 19 union all select p.id, p.name, p.parent_id from products p inner join cte on p.parent_id = cte.id ) select * from cte;Некоторые базы данных имеют альтернативный, нестандартный синтаксис для иерархических поисков, таких как предложение CONNECT BY, доступное в базах данных Oracle. DB2 также поддерживает этот альтернативный синтаксис.
MySql версия 5.7 не предлагает такую функцию. Когда ваш механизм базы данных предоставляет этот синтаксис, то это, безусловно, лучший вариант. Если нет, то также рассмотрим следующие альтернативы.
Альтернатива 2: Идентификаторы стиля пути
Все станет намного проще, если вы присвоите значения id, содержащие иерархическую информацию: путь. Например, в вашем случае это может выглядеть так:
ID | NAME 19 | category1 19/1 | category2 19/1/1 | category3 19/1/1/1 | category4Тогда ваш select будет выглядеть так:
select id, name from products where id like '19/%'Альтернатива 3: Повторные самосоединения
Если вы знаете верхний предел того, насколько глубоким может стать ваше дерево иерархии, вы можете использовать стандартный sql как это:
select p6.parent_id as parent6_id, p5.parent_id as parent5_id, p4.parent_id as parent4_id, p3.parent_id as parent3_id, p2.parent_id as parent2_id, p1.parent_id as parent_id, p1.id as product_id, p1.name from products p1 left join products p2 on p2.id = p1.parent_id left join products p3 on p3.id = p2.parent_id left join products p4 on p4.id = p3.parent_id left join products p5 on p5.id = p4.parent_id left join products p6 on p6.id = p5.parent_id where 19 in (p1.parent_id, p2.parent_id, p3.parent_id, p4.parent_id, p5.parent_id, p6.parent_id) order by 1, 2, 3, 4, 5, 6, 7;Условие where указывает, какой родительский элемент вы хотите получить потомков. Вы можете расширить этот запрос с большим количеством уровней по мере необходимости.
qaru.site
sql-server - Рекурсивный запрос SQL Server
У меня проблема с запросом к одной из наших баз данных MS SQL Server. Следующие таблицы и представления упрощаются ради краткости, но должны служить для описания проблемы.
Каждый балл таблицы составляется как среднее число его непосредственных детей. Представления достаточно для фиксированных структур, но он становится более сложным с иерархией местоположения, которая в настоящее время находится в вложенной заданной форме. В иерархии местоположений нет фиксированного количества уровней, поскольку они определены пользователем.
Я попытался решить это, используя рекурсивный CTE, но они не позволяют агрегации в рекурсивной части.
CREATE TABLE [dbo].[locations_main]( [id] [smallint] NOT NULL, [name] [nchar](50) NOT NULL, [lft] [smallint] NOT NULL, [rgt] [smallint] NOT NULL, [parent_id] [smallint] NULL, CONSTRAINT [PK_locations_main] PRIMARY KEY CLUSTERED ([id] ASC) ) GO INSERT INTO [dbo].[locations_main] VALUES (1, 'location 1', 1, 16, NULL), (2, 'location 1-1', 2, 9, 1), (3, 'location 1-1-1', 3, 4, 2), (4, 'location 1-1-2', 5, 6, 2), (5, 'location 1-1-3', 7, 8, 2), (7, 'location 1-2', 10, 15, 1), (8, 'location 1-2-1', 11, 12, 7), (9, 'location 1-2-2', 13, 14, 7) GO CREATE TABLE [dbo].[outcomes]( [id] [smallint] NOT NULL, [location_id] [smallint] NOT NULL, [name] [nvarchar](50) NOT NULL, CONSTRAINT [PK_outcomes] PRIMARY KEY CLUSTERED ([id] ASC) ) GO INSERT INTO [dbo].[outcomes] VALUES (1, 3, 'outcome 1'), (2, 4, 'outcome 2'), (3, 5, 'outcome 3'), (4, 8, 'outcome 4'), (5, 9, 'outcome 5') GO CREATE TABLE [dbo].[prompts]( [id] [smallint] NOT NULL, [outcome_id] [smallint] NOT NULL, [name] [nvarchar](50) NOT NULL, CONSTRAINT [PK_prompts] PRIMARY KEY CLUSTERED ([id] ASC) ) GO INSERT INTO [dbo].[prompts] VALUES (1, 1, 'prompt 1'), (2, 2, 'prompt 2'), (3, 3, 'prompt 3'), (4, 4, 'prompt 4'), (5, 5, 'prompt 5') GO CREATE TABLE [dbo].[subprompts]( [id] [smallint] NOT NULL, [prompt_id] [smallint] NOT NULL, [name] [nvarchar](50) NOT NULL, [score] [smallint] NOT NULL, CONSTRAINT [PK_subprompts] PRIMARY KEY CLUSTERED ([id] ASC) ) GO INSERT INTO [dbo].[subprompts] VALUES (1, 1, 'subprompt 1', 1), (2, 1, 'subprompt 2', 1), (3, 2, 'subprompt 3', 1), (4, 2, 'subprompt 4', 3), (5, 3, 'subprompt 5', 2), (6, 3, 'subprompt 6', 4), (7, 4, 'subprompt 7', 1), (8, 4, 'subprompt 8', 5), (9, 5, 'subprompt 9', 3), (10, 5, 'subprompt 10', 3) GO CREATE VIEW [dbo].[vw_prompts] AS SELECT dbo.prompts.id, dbo.prompts.outcome_id, dbo.prompts.name, AVG(dbo.subprompts.score) AS score FROM dbo.prompts LEFT OUTER JOIN dbo.subprompts ON dbo.prompts.id = dbo.subprompts.prompt_id GROUP BY dbo.prompts.id, dbo.prompts.outcome_id, dbo.prompts.name GO CREATE VIEW [dbo].[vw_outcomes] AS SELECT dbo.outcomes.id, dbo.outcomes.location_id, dbo.outcomes.name, AVG(dbo.vw_prompts.score) AS score FROM dbo.outcomes LEFT OUTER JOIN dbo.vw_prompts ON dbo.outcomes.id = dbo.vw_prompts.id GROUP BY dbo.outcomes.id, dbo.outcomes.location_id, dbo.outcomes.name GO"местоположение 1" имеет среднее значение "местоположение 1-1-1", "местоположение 1-1-2", "местоположение 1-1-3", "местоположение 1-2-1" и "местоположение 1-2- 2 "- (1 + 2 + 3 + 3 + 3)/5 = 2,4 вместо среднего значения" местоположение 1-1 "и" местоположение 1-2 "- (2 + 3)/2 = 2,5
Я попытался решить эту проблему с помощью CTE, но столкнулся с проблемой использования GROUP BY и агрегатных функций в рекурсивной части CTE -
WITH location_scores AS ( -- Anchor member definition -- Get score for all leaf node locations SELECT locations_main.id, locations_main.name, locations_main.parent_id, AVG(CAST(vw_outcomes.score AS FLOAT)) AS score FROM locations_main INNER JOIN vw_outcomes ON locations_main.id = vw_outcomes.location_id WHERE locations_main.rgt - locations_main.lft = 1 GROUP BY locations_main.id, locations_main.name, locations_main.parent_id UNION ALL -- Recursive member definition -- Rollup through locations parents to build averages SELECT locations_main.id, locations_main.name, locations_main.parent_id, AVG(CAST(location_scores.score AS FLOAT)) AS score FROM locations_main INNER JOIN vw_outcomes ON locations_main.id = vw_outcomes.location_id INNER JOIN location_scores ON locations_main.id = location_scores.parent_id GROUP BY locations_main.id, locations_main.name, locations_main.parent_id ) -- Statement that executes the CTE SELECT * FROM location_scoresЯ был бы очень признателен за некоторые замечания относительно того, является ли это подходящим подходом или нет.
qaru.site
sql - Рекурсивный запрос SQL Server
попробуйте использовать рекурсивный CTE следующим образом:
--this is just an old example I've used before, but it kind of matches your table --go through a nested table supervisor - user table and display the chain DECLARE @Contacts table (id int, first_name varchar(10), reports_to_id int) INSERT @Contacts VALUES (1,'Jerome', NULL ) -- tree is as follows: INSERT @Contacts VALUES (2,'Joe' ,'1') -- 1-Jerome INSERT @Contacts VALUES (3,'Paul' ,'2') -- / \ INSERT @Contacts VALUES (4,'Jack' ,'3') -- 2-Joe 9-Bill INSERT @Contacts VALUES (5,'Daniel','3') -- / \ \ INSERT @Contacts VALUES (6,'David' ,'2') -- 3-Paul 6-David 10-Sam INSERT @Contacts VALUES (7,'Ian' ,'6') -- / \ / \ INSERT @Contacts VALUES (8,'Helen' ,'6') -- 4-Jack 5-Daniel 7-Ian 8-Helen INSERT @Contacts VALUES (9,'Bill ' ,'1') -- INSERT @Contacts VALUES (10,'Sam' ,'9') -- DECLARE @Root_id int --get complete tree--------------------------------------------------- SET @Root_id=null PRINT '@Root_id='+COALESCE(''''+CONVERT(varchar(5),@Root_id)+'''','null') ;WITH StaffTree AS ( SELECT c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf FROM @Contacts c LEFT OUTER JOIN @Contacts cc ON c.reports_to_id=cc.id WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL) UNION ALL SELECT s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1 FROM StaffTree t INNER JOIN @Contacts s ON t.id=s.reports_to_id WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1 ) SELECT * FROM StaffTree ORDER BY LevelOf,first_nameВЫВОД:
@Root_id=null id first_name reports_to_id Manager_id Manager_first_name LevelOf ----------- ---------- ------------- ----------- ------------------ ----------- 1 Jerome NULL NULL NULL 1 9 Bill 1 1 Jerome 2 2 Joe 1 1 Jerome 2 6 David 2 2 Joe 3 3 Paul 2 2 Joe 3 10 Sam 9 9 Bill 3 5 Daniel 3 3 Paul 4 8 Helen 6 6 David 4 7 Ian 6 6 David 4 4 Jack 3 3 Paul 4 (10 row(s) affected)Я бы подумал, что вы можете перебрать результирующий набор в PHP и построить свои DIV.
qaru.site
Рекурсивный SQL-запрос для определения MS SQL Server
См. Ниже DDL:
CREATE TABLE #MasterChild (MasterID decimal(25,0), ChildID decimal(25,0), ChildCreTime datetime) insert into #MasterChild values (150021032000000173536533,150021032000000173946207,'2016-04-22 13:27:30.483') insert into #MasterChild values (150021032000000173571072,150021032000000173946207,'2016-04-22 13:27:30.483') insert into #MasterChild values (150021032000000173573651,150021032000000173946207,'2016-04-22 13:27:30.483') insert into #MasterChild values (150021032000000173574917,150021032000000173946207,'2016-04-22 13:27:30.483') insert into #MasterChild values (150021032000000173582487,150021032000000173946207,'2016-04-22 13:27:30.483') insert into #MasterChild values (150021032000000173604342,150021032000000173946207,'2016-04-22 13:27:30.483') insert into #MasterChild values (150021032000000173931636,150021032000000173946207,'2016-04-22 13:27:30.483') insert into #MasterChild values (150021032000000173935066,150021032000000173946207,'2016-04-22 13:27:30.483') insert into #MasterChild values (150021032000000173946207,150021032000000173952172,'2016-04-22 13:38:32.747') insert into #MasterChild values (150021032000000173946207,150021032000000173954415,'2016-04-22 13:43:28.120') insert into #MasterChild values (150021032000000173536533,150021032000000173954321,'2016-04-22 13:43:28.120') insert into #MasterChild values (150021032000000173954321,150021032000000173954319,'2016-04-22 13:43:28.120')и SQL ниже:
with GetAllMerges as ( select masterid,childid from #MasterChild where masterid=150021032000000173571072 and childid=150021032000000173946207 union all select #MasterChild.masterid,#MasterChild.childid from #MasterChild inner join GetAllMerges on #MasterChild.childid=GetAllMerges.masterid or (#MasterChild.childid=GetAllMerges.childid and #MasterChild.masterid<>GetAllMerges.masterid) ) select distinct masterid,childid from GetAllMergesЯ вижу следующую ошибку:
Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion.Почему это? Я пытаюсь показать всех связанных с ними детей и мастеров. Например, если мастер 1 связан с дочерним элементом 2, а ребенок 2 связан с мастером 3, а мастер 3 связан с ребенком 4, а ребенок 4 связан с мастером 5, то лица 1-5 связаны (все дети и мастера являются лицами ).
Solutions Collecting From Web of "Рекурсивный SQL-запрос для определения"
Рекурсивные CTE, как вы пытаетесь сделать здесь, имеют подразумеваемую MAXRECURSION 100, прежде чем они выйдут из строя.
Вы можете указать, сколько рекурсий вы хотите использовать с помощью OPTION (MAXRECURSION n) WHERE n – это число от 0 до 32767, причем 0 в основном означает выполнение до тех пор, пока не будет выполнено какое-либо мероприятие независимо от того, сколько времени потребуется.
Я НЕ ПРЕДУСМОТРЕТЬ ЭТО В ВАШЕМ СЛУЧАЕ
У вашего CTE нет прерывания для его рекурсивного элемента
Неправильно составленный рекурсивный CTE может вызвать бесконечный цикл . Например, если определение запроса рекурсивного члена возвращает одинаковые значения для родительского и дочернего столбцов, создается бесконечный цикл. Чтобы предотвратить бесконечный цикл, вы можете ограничить количество уровней рекурсии, разрешенных для конкретного оператора, с помощью подсказки MAXRECURSION и значения от 0 до 32,767 в предложении OPTION операторов INSERT, UPDATE, DELETE или SELECT. Это позволяет вам управлять выполнением инструкции до тех пор, пока вы не устраните проблему с кодом, которая создает цикл. По умолчанию для сервера установлено значение 100. Когда указано 0, предел не применяется. Только одно значение MAXRECURSION может быть указано для каждого оператора.
(Добавлен акцент)
Прежде чем перейти к изменению настроек MAXRECURSION запрос с правильным завершением рекурсивного CTE.
Позвольте мне понять, понимаю ли я ваш метод:
Вы пишете (GetAllMerges), чтобы отобразить нужные данные, и НАЧАТЬ функцию, которую вы ссылаетесь на нее рекурсивно?
==> # MasterChild.childid = GetAllMerges.masterid <===
Я думаю, вы просто создали бесконечный цикл.
Я не уверен, почему вы не просто используете какое-то время или делаете, чтобы переходить через связанные ключи?
Кроме того, вы можете и должны использовать псевдонимы, если имена ваших таблиц так долго.
Вы должны переключить childid и parentid в свое «внутреннее соединение» следующим образом:
Попробуйте
with GetAllMerges as ( select masterid,childid,1 AS RecLevel from #MasterChild where masterid=150021032000000173571072 and childid=150021032000000173946207 union all select #MasterChild.masterid,#MasterChild.childid,GetAllMerges.RecLevel + 1 from #MasterChild inner join GetAllMerges on #MasterChild.masterid=GetAllMerges.ChildID --or (#MasterChild.childid=GetAllMerges.childid and #MasterChild.masterid<>GetAllMerges.masterid) ) select distinct masterid,childid from GetAllMergesЯ прокомментировал or (#MasterChild... прочь, так как это вызывает ошибку … Просьба описать, чего вы пытаетесь достичь …
sqlserver.bilee.com
(CTE) Рекурсивный SQL-запрос MS SQL Server
Я смущен некоторой проблемой, связанной с рекурсивным запросом. Я использую SQL SERVER 2012 Мой scnerio, местоположения определены иерархические, у каждого местоположения есть свой собственный актив. Таблица My Locations like;
Id | Name | ParentLocationId ----+------+----------------- 1 | L1 | NULL 2 | L2 | 1 3 | L3 | 1 4 | L4 | 1 5 | L5 | 1 6 | L6 | 4 7 | L7 | 4 8 | L8 | 4 9 | L9 | 2 10 | L10 | 2 11 | L11 | 6 12 | L12 | 6 13 | L13 | 6Моя таблица активов вроде;
Id | AssetNo | Description | CurrentLocationId -------+---------+-------------+------------------ 1 | AN001 | ADesc | 1 2 | AN002 | BDesc | 1L1 имеет 1, L2 имеет 2, L3 – 0, L4 – 3, L5 – 5, L6 – 5, L7 – 1, L8 – 0, L9 – 3, L10 – 2, L11 – 5, L12 – 3, L13 – 6 Активы
Мой вопрос в том, как я могу взять общее количество подсчитанных активов на уровне 1 выбранного уровня?
Например; Выбранный параметр LocationId = 1 (L1) Sample Output;
Id | Name | Qty -------+------+----- 2 | L2 | 7 3 | L3 | 0 4 | L4 | 23 5 | L5 | 5 - Id | Name | Qty -------+------+----- 2 | L2 | 7 3 | L3 | 0 4 | L4 | 23 5 | L5 | 5Другой пример; Выбранное местоположениеId = 4 (L4)
Id | Name | Qty ---+------+----- 6 | L6 | 19 7 | L7 | 1 8 | L8 | 0 - Id | Name | Qty ---+------+----- 6 | L6 | 19 7 | L7 | 1 8 | L8 | 0Я пытаюсь написать запрос,
WITH recursiveTable AS (SELECT * FROM location l WHERE ParentLocationId = 1 UNION ALL SELECT l.* FROM location l INNER JOIN recursiveTable r ON r.Id = l.ParentLocationId), allLocations AS (SELECT * FROM recursiveTable UNION SELECT * FROM Location WHERE Id = 0), resultset AS (SELECT r.NAME AS LocationName, a.* FROM allLocations r INNER JOIN Asset a ON a.CurrentLocationId = r.Id WHERE r.DataStatus = 1) select CurrentLocationId ,min(LocationName) as LocationName ,count(Id) as NumberOfAsset from resultset group by CurrentLocationIdДополнительно;
Create Table Location ( Id int, Name nvarchar(100), Description nvarchar(250), ParentLocationId int, DataStatus int ) Create Table Asset ( Id int, AssetNo nvarchar(50), Description nvarchar(250), CurrentLocationId int, DataStatus int ) Insert Into Location Values(1,'L1','LDesc1',NULL,1) Insert Into Location Values(2,'L2','LDesc2',1,1) Insert Into Location Values(3,'L3','LDesc3',1,1) Insert Into Location Values(4,'L4','LDesc4',1,1) Insert Into Location Values(5,'L5','LDesc5',1,1) Insert Into Location Values(6,'L6','LDesc6',4,1) Insert Into Location Values(7,'L7','LDesc7',4,1) Insert Into Location Values(8,'L8','LDesc8',4,1) Insert Into Location Values(9,'L9','LDesc9',2,1) Insert Into Location Values(10,'L10','LDesc10',2,1) Insert Into Location Values(11,'L11','LDesc11',6,1) Insert Into Location Values(12,'L12','LDesc12',6,1) Insert Into Location Values(13,'L13','LDesc13',6,1) Insert Into Asset Values (1,'FDB-001','Desc1',1,1) Insert Into Asset Values (2,'FDB-002','Desc2',2,1) Insert Into Asset Values (3,'FDB-003','Desc3',2,1) Insert Into Asset Values (4,'FDB-004','Desc4',4,1) Insert Into Asset Values (5,'FDB-005','Desc5',4,1) Insert Into Asset Values (6,'FDB-006','Desc6',4,1) Insert Into Asset Values (7,'FDB-007','Desc7',5,1) Insert Into Asset Values (8,'FDB-008','Desc8',5,1) Insert Into Asset Values (9,'FDB-009','Desc9',5,1) Insert Into Asset Values (10,'FDB-010','Desc10',5,1) Insert Into Asset Values (11,'FDB-011','Desc11',5,1) Insert Into Asset Values (12,'FDB-012','Desc12',6,1) Insert Into Asset Values (13,'FDB-013','Desc13',6,1) Insert Into Asset Values (14,'FDB-014','Desc14',6,1) Insert Into Asset Values (15,'FDB-015','Desc15',6,1) Insert Into Asset Values (16,'FDB-016','Desc16',6,1) Insert Into Asset Values (17,'FDB-017','Desc17',7,1) Insert Into Asset Values (18,'FDB-018','Desc18',9,1) Insert Into Asset Values (19,'FDB-019','Desc19',9,1) Insert Into Asset Values (20,'FDB-020','Desc20',9,1) Insert Into Asset Values (21,'FDB-021','Desc21',10,1) Insert Into Asset Values (22,'FDB-022','Desc22',10,1) Insert Into Asset Values (23,'FDB-023','Desc23',11,1) Insert Into Asset Values (24,'FDB-024','Desc24',11,1) Insert Into Asset Values (25,'FDB-025','Desc25',11,1) Insert Into Asset Values (26,'FDB-026','Desc26',11,1) Insert Into Asset Values (27,'FDB-027','Desc27',11,1) Insert Into Asset Values (28,'FDB-028','Desc28',12,1) Insert Into Asset Values (29,'FDB-029','Desc29',12,1) Insert Into Asset Values (30,'FDB-030','Desc30',12,1) Insert Into Asset Values (31,'FDB-031','Desc31',13,1) Insert Into Asset Values (32,'FDB-032','Desc32',13,1) Insert Into Asset Values (33,'FDB-033','Desc33',13,1) Insert Into Asset Values (34,'FDB-034','Desc34',13,1) Insert Into Asset Values (35,'FDB-035','Desc35',13,1) Insert Into Asset Values (36,'FDB-036','Desc36',13,1)С наилучшими пожеланиями,
Solutions Collecting From Web of "(CTE) Рекурсивный SQL-запрос"
Мы можем применить Level и Path чтобы попытаться подсчитать ваш ребенок, но отображать только первый уровень детей. В итоге мы группируем количество активов по пути, который является идентификатором первого уровня детей. Затем выберите только первый уровень в конце
DECLARE @LocationID INT = 1; WITH recursiveCTE AS ( SELECT *, 1 AS [Level], Id [Path] FROM location l WHERE l.ParentLocationId = @LocationID UNION ALL SELECT l.*, [Level] + 1, [Path] FROM location l JOIN recursiveCTE r ON l.ParentLocationId = r.Id ), countCte AS ( SELECT [Path] Id, COUNT(a.AssetNo) Qty FROM recursiveCTE c JOIN Asset a ON c.Id = a.CurrentLocationId GROUP BY [Path] ) SELECT r.Id, r.[Name], COALESCE(c.Qty,0) Qty FROM recursiveCTE r LEFT JOIN countCte c ON r.Id = c.Id WHERE r.[Level] = 1;sqlserver.bilee.com
tsql - Как спроектировать SQL-рекурсивный запрос?
Как бы я перепроектировал следующий запрос так, чтобы он рекурсивно перебирал все дерево, чтобы вернуть всех потомков от корня до листьев? (Я использую SSMS 2008). У нас есть корень. под ним находятся ВП, затем высшее руководство и т.д., на линии. Мне нужно вернуть имена и названия каждого из них. Но этот запрос не должен быть жестко закодирован; Мне нужно иметь возможность запускать это для любого выбранного сотрудника, а не только для президента. Этот запрос ниже - это жесткий код.
select P.staff_name [Level1], P.job_title [Level1 Title], Q.license_number [License 1], E.staff_name [Level2], E.job_title [Level2 Title], G.staff_name [Level3], G.job_title [Level3 Title] from staff_view A left join staff_site_link_expanded_view P on P.people_id = A.people_id left join staff_site_link_expanded_view E on E.people_id = C.people_id left join staff_site_link_expanded_view G on G.people_id = F.people_id left join facility_view Q on Q.group_profile_id = P.group_profile_idСпасибо, это было наиболее точно сопоставимо с тем, что мне нужно. Вот мой CTE-запрос ниже:
with Employee_Hierarchy (staff_name, job_title, id_number, billing_staff_credentials_code, site_name, group_profile_id, license_number, region_description, people_id) as ( select C.staff_name, C.job_title, C.id_number, C.billing_staff_credentials_code, C.site_name, C.group_profile_id, Q.license_number, R.region_description, A.people_id from staff_view A left join staff_site_link_expanded_view C on C.people_id = A.people_id left join facility_view Q on Q.group_profile_id = C.group_profile_id left join regions R on R.regions_id = Q.regions_id where A.last_name = 'kromer' ) select C.staff_name, C.job_title, C.id_number, C.billing_staff_credentials_code, C.site_name, C.group_profile_id, Q.license_number, R.region_description, A.people_id from staff_view A left join staff_site_link_expanded_view C on C.people_id = A.people_id left join facility_view Q on Q.group_profile_id = C.group_profile_id left join regions R on R.regions_id = Q.regions_id WHERE C.STAFF_NAME IS NOT NULL GROUP BY C.STAFF_NAME, C.job_title, C.id_number, C.billing_staff_credentials_code, C.site_name, C.group_profile_id, Q.license_number, R.region_description, A.people_id ORDER BY C.STAFF_NAMEНо мне интересно, какова цель "Employee_Hierarchy"? Когда я заменил "staff_view" во внешнем запросе на "Employee_Hierarchy", он возвратил только одну запись = "Kromer". Итак, когда/где мы можем использовать "Employee_Hierarchy"?
qaru.site
