Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Друзья! Помогите составить рекурсивный запрос.  [new]
IvanVetrov
Member

Откуда: Брянск
Сообщений: 51
Помогите составить рекурсивный запрос, который бы вывел номер поколение каждого элемента . Во вложении база ACCESS, в которой 2 таблицы. Одна из них tNasled содержит иерархию образования элементов друг из друга, путём объединения и деления.

Пример:

из 1 образовалось 2 и 3, из 2 образовалось 4 и 5, из 3 образовалось 6 и 7, из 7,6,4,5 образовалось 8... Итого, 1 - это первое поколение, так как у него нет предков. 2, 3 - это второе поколение, так как у них есть предок 1 поколения, 7,6,4,5 - это третье поколение, так как у них есть предки 2uj поколения, 8 - это 4е поколение...

Каким должен быть запрос на MS SQL Server 2012, чтобы вывести таблицу вида:

Элемент | Поколение
1 | 1
2 | 2
3 | 2
4 | 3
5 | 3
6 | 3
7 | 3
8 | 4

К сообщению приложен файл (ierarhy.rar - 17Kb) cкачать
10 июн 14, 17:22    [16150544]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
В справке, топик WITH обобщенное_табличное_выражение, есть пример рекурсивного запроса.
10 июн 14, 17:43    [16150653]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
IvanVetrov
Member

Откуда: Брянск
Сообщений: 51
Владислав,
я знаю, что with есть в справке. А ближе к реализации есть ответ?
11 июн 14, 08:47    [16152274]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
Glory
Member

Откуда:
Сообщений: 104751
IvanVetrov
А ближе к реализации есть ответ?

Что ни один из примеров вам не подошел ?
11 июн 14, 09:42    [16152497]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
IvanVetrov
Member

Откуда: Брянск
Сообщений: 51
Glory, увы, нет. Ни один из примеров мне не подошёл
11 июн 14, 09:49    [16152557]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
Glory
Member

Откуда:
Сообщений: 104751
IvanVetrov
Ни один из примеров мне не подошёл

Неужели у вас настолько уникальная иерархия ?
11 июн 14, 09:55    [16152584]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
IvanVetrov
Member

Откуда: Брянск
Сообщений: 51
Glory, ну так посмотрите сами, во вложении есть база на аксе с этой иерархией. Сложность её в том, что потомок может содержать предков разных поколений. Например, Картинка с другого сайта..


Элемент 6 состоит из 2 элементов 3го поколения 4,5 и из одного элемента 2 поколения - 2. Сам 6й элемент 4го поколения
11 июн 14, 10:26    [16152763]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
IvanVetrov
Member

Откуда: Брянск
Сообщений: 51
Вложил схему

К сообщению приложен файл. Размер - 14Kb
11 июн 14, 10:29    [16152775]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
бэда
Guest
IvanVetrov,

напишите скрипты создания данных вместе с объектами.
типа
declare @table ()..
insert into @table blablabal..
11 июн 14, 10:29    [16152776]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
Glory
Member

Откуда:
Сообщений: 104751
IvanVetrov
ну так посмотрите сами, во вложении есть база на аксе с этой иерархией.

Т.е. это _мне_ нужно
- установить аксесс
- установить winrar
- скачать файл
- запустить базу
11 июн 14, 10:30    [16152785]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
IvanVetrov
Member

Откуда: Брянск
Сообщений: 51
виноват.
Так подойдёт? Полный скрипт внутри

USE [test]
GO
/****** Object:  Table [dbo].[tNasled]    Script Date: 11.06.2014 10:56:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tNasled](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[parent] [int] NOT NULL,
	[child] [int] NOT NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tPol]    Script Date: 11.06.2014 10:56:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tPol](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](255) NOT NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[tNasled] ON 

INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (3, 1, 2)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (4, 1, 3)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (8, 2, 4)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (5, 2, 5)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (9, 3, 6)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (10, 3, 7)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (11, 4, 8)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (12, 5, 8)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (13, 6, 8)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (14, 7, 8)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (15, 8, 9)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (16, 8, 10)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (17, 8, 11)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (18, 8, 12)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (21, 9, 15)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (22, 10, 15)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (19, 11, 13)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (20, 11, 14)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (26, 12, 16)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (24, 13, 16)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (25, 14, 16)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (23, 15, 16)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (27, 16, 17)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (28, 16, 18)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (29, 16, 19)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (30, 16, 20)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (31, 17, 21)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (33, 18, 22)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (34, 19, 22)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (32, 20, 21)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (39, 21, 23)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (35, 21, 24)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (37, 21, 26)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (36, 22, 24)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (40, 22, 25)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (38, 22, 26)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (41, 23, 31)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (42, 23, 32)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (43, 24, 29)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (44, 24, 30)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (45, 26, 33)
INSERT [dbo].[tNasled] ([id], [parent], [child]) VALUES (48, 26, 34)
SET IDENTITY_INSERT [dbo].[tNasled] OFF
SET IDENTITY_INSERT [dbo].[tPol] ON 

INSERT [dbo].[tPol] ([id], [name]) VALUES (17, N'bg')
INSERT [dbo].[tPol] ([id], [name]) VALUES (27, N'cd')
INSERT [dbo].[tPol] ([id], [name]) VALUES (33, N'cf')
INSERT [dbo].[tPol] ([id], [name]) VALUES (10, N'df')
INSERT [dbo].[tPol] ([id], [name]) VALUES (1, N'dw')
INSERT [dbo].[tPol] ([id], [name]) VALUES (9, N'ed')
INSERT [dbo].[tPol] ([id], [name]) VALUES (3, N'ee')
INSERT [dbo].[tPol] ([id], [name]) VALUES (16, N'ew')
INSERT [dbo].[tPol] ([id], [name]) VALUES (28, N'fd')
INSERT [dbo].[tPol] ([id], [name]) VALUES (34, N'ff')
INSERT [dbo].[tPol] ([id], [name]) VALUES (21, N'fr')
INSERT [dbo].[tPol] ([id], [name]) VALUES (11, N'gf')
INSERT [dbo].[tPol] ([id], [name]) VALUES (23, N'hg')
INSERT [dbo].[tPol] ([id], [name]) VALUES (5, N'hh')
INSERT [dbo].[tPol] ([id], [name]) VALUES (12, N'hy')
INSERT [dbo].[tPol] ([id], [name]) VALUES (8, N'ii')
INSERT [dbo].[tPol] ([id], [name]) VALUES (20, N'ki')
INSERT [dbo].[tPol] ([id], [name]) VALUES (24, N'kj')
INSERT [dbo].[tPol] ([id], [name]) VALUES (22, N'nh')
INSERT [dbo].[tPol] ([id], [name]) VALUES (14, N're')
INSERT [dbo].[tPol] ([id], [name]) VALUES (4, N'rr')
INSERT [dbo].[tPol] ([id], [name]) VALUES (29, N'rt')
INSERT [dbo].[tPol] ([id], [name]) VALUES (25, N'ru')
INSERT [dbo].[tPol] ([id], [name]) VALUES (13, N'sd')
INSERT [dbo].[tPol] ([id], [name]) VALUES (7, N'tr')
INSERT [dbo].[tPol] ([id], [name]) VALUES (15, N'uk')
INSERT [dbo].[tPol] ([id], [name]) VALUES (19, N'vf')
INSERT [dbo].[tPol] ([id], [name]) VALUES (2, N'wd')
INSERT [dbo].[tPol] ([id], [name]) VALUES (18, N'we')
INSERT [dbo].[tPol] ([id], [name]) VALUES (6, N'wr')
INSERT [dbo].[tPol] ([id], [name]) VALUES (32, N'xs')
INSERT [dbo].[tPol] ([id], [name]) VALUES (26, N'yt')
INSERT [dbo].[tPol] ([id], [name]) VALUES (31, N'zx')
INSERT [dbo].[tPol] ([id], [name]) VALUES (30, N'zz')
SET IDENTITY_INSERT [dbo].[tPol] OFF
/****** Object:  Index [aaaaatNasled_PK]    Script Date: 11.06.2014 10:56:13 ******/
ALTER TABLE [dbo].[tNasled] ADD  CONSTRAINT [aaaaatNasled_PK] PRIMARY KEY NONCLUSTERED 
(
	[parent] ASC,
	[child] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON

GO
/****** Object:  Index [aaaaatPol_PK]    Script Date: 11.06.2014 10:56:13 ******/
ALTER TABLE [dbo].[tPol] ADD  CONSTRAINT [aaaaatPol_PK] PRIMARY KEY NONCLUSTERED 
(
	[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tNasled]  WITH NOCHECK ADD  CONSTRAINT [tNasled_FK00] FOREIGN KEY([parent])
REFERENCES [dbo].[tPol] ([id])
GO
ALTER TABLE [dbo].[tNasled] NOCHECK CONSTRAINT [tNasled_FK00]
GO
ALTER TABLE [dbo].[tNasled]  WITH NOCHECK ADD  CONSTRAINT [tNasled_FK01] FOREIGN KEY([child])
REFERENCES [dbo].[tPol] ([id])
GO
ALTER TABLE [dbo].[tNasled] NOCHECK CONSTRAINT [tNasled_FK01]


К сообщению приложен файл (hierarhy.sql - 81Kb) cкачать
11 июн 14, 11:02    [16153037]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
Glory
Member

Откуда:
Сообщений: 104751
IvanVetrov
Вложил схему

У вас не иерархия.
У вас граф.
11 июн 14, 11:18    [16153158]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8815
IvanVetrov, иерархия не имеет несколько "отцов" для одного "ребенка". Судя по картинке, это условие не выполняется.
11 июн 14, 11:40    [16153377]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
aleks2
Guest
Владислав Колосов
IvanVetrov, иерархия не имеет несколько "отцов" для одного "ребенка". Судя по картинке, это условие не выполняется.

У него шведская семья.
11 июн 14, 11:44    [16153429]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
aleks2
Guest
Задача тредстартера тривиальна.
Рекурсии не надо (хотя, канешно, можно микроскопом гвозди заколачивать).

Надо update в цикле. И фсе.
11 июн 14, 11:48    [16153468]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
IvanVetrov
Member

Откуда: Брянск
Сообщений: 51
aleks2, не могли бы поподробнее, ближе к коду?
11 июн 14, 11:58    [16153548]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
IvanVetrov
Member

Откуда: Брянск
Сообщений: 51
Владислав Колосов, может и не имеет нескольких отцов, но она может иметь мать и отца.
11 июн 14, 12:01    [16153574]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
msLex
Member

Откуда:
Сообщений: 9282
берёте стандартный вариант обхода иерархии из хелпа, на выходе у вас для некоторых нод получиться несколько записей с разными значениями поле "поколение", сгруппируйте и выберете одно (максимальное например).
11 июн 14, 12:06    [16153618]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
IvanVetrov
Member

Откуда: Брянск
Сообщений: 51
msLex, благодарю
11 июн 14, 12:22    [16153758]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
aleks2
Guest
Тредстартер не сможет ничо наваять.
Он даже не въехал, что рекурсия тут бессильна.

set nocount on

declare @tNasled TABLE(
	[id] [int]  NOT NULL,
	[parent] [int] NOT NULL,
	[child] [int] NOT NULL,
	[generation] int
);


INSERT @tNasled ([id], [parent], [child]) VALUES (3, 1, 2)
INSERT @tNasled ([id], [parent], [child]) VALUES (4, 1, 3)
INSERT @tNasled ([id], [parent], [child]) VALUES (8, 2, 4)
INSERT @tNasled ([id], [parent], [child]) VALUES (5, 2, 5)
INSERT @tNasled ([id], [parent], [child]) VALUES (9, 3, 6)
INSERT @tNasled ([id], [parent], [child]) VALUES (10, 3, 7)
INSERT @tNasled ([id], [parent], [child]) VALUES (11, 4, 8)
INSERT @tNasled ([id], [parent], [child]) VALUES (12, 5, 8)
INSERT @tNasled ([id], [parent], [child]) VALUES (13, 6, 8)
INSERT @tNasled ([id], [parent], [child]) VALUES (14, 7, 8)
INSERT @tNasled ([id], [parent], [child]) VALUES (15, 8, 9)
INSERT @tNasled ([id], [parent], [child]) VALUES (16, 8, 10)
INSERT @tNasled ([id], [parent], [child]) VALUES (17, 8, 11)
INSERT @tNasled ([id], [parent], [child]) VALUES (18, 8, 12)
INSERT @tNasled ([id], [parent], [child]) VALUES (21, 9, 15)
INSERT @tNasled ([id], [parent], [child]) VALUES (22, 10, 15)
INSERT @tNasled ([id], [parent], [child]) VALUES (19, 11, 13)
INSERT @tNasled ([id], [parent], [child]) VALUES (20, 11, 14)
INSERT @tNasled ([id], [parent], [child]) VALUES (26, 12, 16)
INSERT @tNasled ([id], [parent], [child]) VALUES (24, 13, 16)
INSERT @tNasled ([id], [parent], [child]) VALUES (25, 14, 16)
INSERT @tNasled ([id], [parent], [child]) VALUES (23, 15, 16)
INSERT @tNasled ([id], [parent], [child]) VALUES (27, 16, 17)
INSERT @tNasled ([id], [parent], [child]) VALUES (28, 16, 18)
INSERT @tNasled ([id], [parent], [child]) VALUES (29, 16, 19)
INSERT @tNasled ([id], [parent], [child]) VALUES (30, 16, 20)
INSERT @tNasled ([id], [parent], [child]) VALUES (31, 17, 21)
INSERT @tNasled ([id], [parent], [child]) VALUES (33, 18, 22)
INSERT @tNasled ([id], [parent], [child]) VALUES (34, 19, 22)
INSERT @tNasled ([id], [parent], [child]) VALUES (32, 20, 21)
INSERT @tNasled ([id], [parent], [child]) VALUES (39, 21, 23)
INSERT @tNasled ([id], [parent], [child]) VALUES (35, 21, 24)
INSERT @tNasled ([id], [parent], [child]) VALUES (37, 21, 26)
INSERT @tNasled ([id], [parent], [child]) VALUES (36, 22, 24)
INSERT @tNasled ([id], [parent], [child]) VALUES (40, 22, 25)
INSERT @tNasled ([id], [parent], [child]) VALUES (38, 22, 26)
INSERT @tNasled ([id], [parent], [child]) VALUES (41, 23, 31)
INSERT @tNasled ([id], [parent], [child]) VALUES (42, 23, 32)
INSERT @tNasled ([id], [parent], [child]) VALUES (43, 24, 29)
INSERT @tNasled ([id], [parent], [child]) VALUES (44, 24, 30)
INSERT @tNasled ([id], [parent], [child]) VALUES (45, 26, 33)
INSERT @tNasled ([id], [parent], [child]) VALUES (48, 26, 34)


declare @rc int;

update n set generation = 0
  from  @tNasled n where not exists(select * from @tNasled where child = n.parent);
set @rc = @@rowcount;

while @rc > 0
begin

    update t set generation = x.generation 
     from  @tNasled t right outer join
           (select nn.id, max(n.generation)+1 generation 
              from  (select * from @tNasled where generation is not null ) n inner join @tNasled nn on nn.parent = n.child
              group by nn.id 
            ) x
            on x.id = t.id 
     where t.generation <> x.generation or t.generation is null;
     set @rc = @@rowcount;
  
end;

select * from @tNasled ;
11 июн 14, 12:31    [16153835]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
IvanVetrov
Member

Откуда: Брянск
Сообщений: 51
aleks2, вы просто монстр! Обнимаю!
11 июн 14, 15:46    [16155514]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
IvanVetrov
aleks2, вы просто монстр! Обнимаю!
Monster
Существительное

1. чудовище; изверг
2. урод
3. что-л. или кто-л. огромного размера, гигант
4. монстр
5. что-л. громадное
6. воен. жарг. атомная или водородная бомба
7. уродина

http://slovari.yandex.ru/monster/en-ru/#lingvo/

Похвалил, ничего не скажешь!
11 июн 14, 15:59    [16155631]     Ответить | Цитировать Сообщить модератору
 Re: Друзья! Помогите составить рекурсивный запрос.  [new]
IvanVetrov
Member

Откуда: Брянск
Сообщений: 51
iap, в значение гигант мысли
11 июн 14, 17:18    [16156238]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить