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

Откуда:
Сообщений: 867
Коллеги, помогите составить рекурсивный запрос.
Про рекурсивные CTE, конечно, читал, но составить запрос не могу. Просто не умею мыслить такими категориями.
Нужен пример и всё такое.

Есть таблица:
CREATE TABLE parent_child (
	[N] [int] IDENTITY(1,1) NOT NULL,
	[parent_id] [varchar](10) NOT NULL,
	[parent_rng] [int] NOT NULL,
	[child_id] [varchar](10) NOT NULL,
	[child_rng] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[parent_id] ASC,
	[child_id] ASC,
	[N] ASC
))

INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('_1Z00JIJI2',0,'_1Z00JIJI2',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('_1Z00JIJI2',0,'_1Z00JIJI2',1)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('_1Z00JIJI2',1,'B1YD13B28O',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('B1YD13B28O',0,'B1YD13BVHE',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('C2780K704W',0,'C1YD128OPG',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('C2780K704W',0,'C1YD12IK1E',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('C2780K704W',0,'C2780K704W',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('E33W0L3XYY',0,'E1YD12P8FU',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('E33W0L3XYY',0,'E1YD12WA3I',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('E33W0L3XYY',0,'E1YD1312WY',0)


Пара [parent_id],[parent_rng] и [child_id],[child_rng] - это идентификаторы узлов дерева.

Корни деревьев (их множество) это узлы, в которых [parent_id]=[child_id] and [parent_rng]=[child_rng], т.е. одинаковые значения в parent и child половинках.
Дочерние узлы содержат значения [parent_id],[parent_rng] родительского узла, и [child_id],[child_rng] - своего.
Например:
('_1Z00JIJI2',0,'_1Z00JIJI2',0) - корневой узел
('_1Z00JIJI2',0,'_1Z00JIJI2',1) - дочерний
('_1Z00JIJI2',1,'B1YD13B28O',0) - "внучатый"
И т.д., вложенность около 1000

Задача:
Раскрыть дерево в таблицу "общий предок" - "текущий узел".
Т.е. должно быть:

('_1Z00JIJI2',0,'_1Z00JIJI2',0)
('_1Z00JIJI2',0,'_1Z00JIJI2',1)
('_1Z00JIJI2',0,'B1YD13B28O',0)

Я знаю, что это просто. Но не могу уложить это в голове.
Помогите!
14 июл 16, 16:33    [19410032]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить рекурсивный запрос  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
uaggster,

В справке есть хороший пример. Попробуйте разобраться.
14 июл 16, 16:35    [19410043]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить рекурсивный запрос  [new]
uaggster
Member

Откуда:
Сообщений: 867
Владислав Колосов, я пробовал, не получается.
14 июл 16, 16:53    [19410204]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить рекурсивный запрос  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
uaggster,

странноватое дерево, но как-то так
--DROP TABLE parent_child 
CREATE TABLE parent_child (
	[N] [int] IDENTITY(1,1) NOT NULL,
	[parent_id] [varchar](10) NOT NULL,
	[parent_rng] [int] NOT NULL,
	[child_id] [varchar](10) NOT NULL,
	[child_rng] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[parent_id] ASC,
	[child_id] ASC,
	[N] ASC
))


INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('_1Z00JIJI2',0,'_1Z00JIJI2',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('_1Z00JIJI2',0,'_1Z00JIJI2',1)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('_1Z00JIJI2',1,'B1YD13B28O',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('B1YD13B28O',0,'B1YD13BVHE',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('C2780K704W',0,'C1YD128OPG',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('C2780K704W',0,'C1YD12IK1E',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('C2780K704W',0,'C2780K704W',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('E33W0L3XYY',0,'E1YD12P8FU',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('E33W0L3XYY',0,'E1YD12WA3I',0)
INSERT INTO [parent_child]([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('E33W0L3XYY',0,'E1YD1312WY',0)

;WITH cte
AS
(
	SELECT 
		[Level] = 0,
		MasterId = a.[child_id]+STR(a.[child_rng]),
		a.*
	FROM parent_child	a
	WHERE 
		parent_id = child_id	AND
		[parent_rng] = [child_rng]
	UNION ALL

	SELECT 
		b.[level] + 1, 
		MasterId = a.[child_id]+STR(a.[child_rng]),
		a.*
	FROM 
		parent_child	a
	JOIN
		cte		b
	ON
		b.MasterId !=a.[child_id]+STR(a.[child_rng])	AND				
		b.MasterId = a.[parent_id]+STR(a.[parent_rng])
) 
SELECT 
*
FROM cte
ORDER BY
	parent_id,parent_rng 
	
14 июл 16, 17:20    [19410318]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить рекурсивный запрос  [new]
uaggster
Member

Откуда:
Сообщений: 867
Нет, TaPaK, это не правильно.
Вот результат твоего CTE:

Level MasterId N parent_id parent_rng child_id child_rng
0 _1Z00JIJI2 0 1 _1Z00JIJI2 0 _1Z00JIJI2 0
1 _1Z00JIJI2 1 2 _1Z00JIJI2 0 _1Z00JIJI2 1
2 B1YD13B28O 0 3 _1Z00JIJI2 1 B1YD13B28O 0
3 B1YD13BVHE 0 4 B1YD13B28O 0 B1YD13BVHE 0
0 C2780K704W 0 7 C2780K704W 0 C2780K704W 0
1 C1YD128OPG 0 5 C2780K704W 0 C1YD128OPG 0
1 C1YD12IK1E 0 6 C2780K704W 0 C1YD12IK1E 0

Даже если выводить только parent_id parent_rng child_id child_rng, то уже вот эта строка:
2 B1YD13B28O 0 3 _1Z00JIJI2 1 B1YD13B28O 0 - лишняя

Должно выводиться:
('_1Z00JIJI2',0,'B1YD13B28O',0)
Т.е. - "общий предок для ВСЕХ узлов дерева" - "код текущего узла" (для корня это сам корень, для узлов - значение из child_id, child_rng).

Ну а странное - ну ключ там составной. Из двух полей. Ну, бывает. Легаси.
14 июл 16, 17:36    [19410372]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить рекурсивный запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
declare @parent_child table (
	[N] [int] IDENTITY(1,1) NOT NULL,
	[parent_id] [varchar](10) NOT NULL,
	[parent_rng] [int] NOT NULL,
	[child_id] [varchar](10) NOT NULL,
	[child_rng] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[parent_id] ASC,
	[child_id] ASC,
	[N] ASC
));

INSERT INTO @parent_child([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('_1Z00JIJI2',0,'_1Z00JIJI2',0);
INSERT INTO @parent_child([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('_1Z00JIJI2',0,'_1Z00JIJI2',1);
INSERT INTO @parent_child([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('_1Z00JIJI2',1,'B1YD13B28O',0);
INSERT INTO @parent_child([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('B1YD13B28O',0,'B1YD13BVHE',0);
INSERT INTO @parent_child([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('C2780K704W',0,'C1YD128OPG',0);
INSERT INTO @parent_child([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('C2780K704W',0,'C1YD12IK1E',0);
INSERT INTO @parent_child([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('C2780K704W',0,'C2780K704W',0);
INSERT INTO @parent_child([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('E33W0L3XYY',0,'E1YD12P8FU',0);
INSERT INTO @parent_child([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('E33W0L3XYY',0,'E1YD12WA3I',0);
INSERT INTO @parent_child([parent_id],[parent_rng],[child_id],[child_rng]) VALUES('E33W0L3XYY',0,'E1YD1312WY',0);

with s as
(
 select
  parent_id as parent_id_root, parent_rng as parent_rng_root, parent_id, parent_rng, child_id, child_rng
 from
  @parent_child
 where
  parent_id = child_id and parent_rng = child_rng

 union all

 select
  s.parent_id_root, s.parent_rng_root, pc.parent_id, pc.parent_rng, pc.child_id, pc.child_rng
 from
  s join
  @parent_child pc on pc.parent_id = s.child_id and pc.parent_rng = s.child_rng
 where
  pc.parent_id <> pc.child_id or pc.parent_rng <> pc.child_rng
)
select parent_id_root, parent_rng_root, child_id, child_rng from s;
14 июл 16, 19:18    [19410814]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить рекурсивный запрос  [new]
uaggster
Member

Откуда:
Сообщений: 867
invm, спасибо!
Оно, вроде, работает как надо, Только я всё равно не могу понять, как оно работает!
Хоть убей. Блин.
14 июл 16, 19:48    [19410926]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить рекурсивный запрос  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
uaggster
Только я всё равно не могу понять, как оно работает!
Объяснение
15 июл 16, 10:25    [19412844]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить