Итак, я начал с того, вытащил категорию номенклатуры, которая мне нужна и сохранил в переменной:
`DECLARE @rcs_group AS binary(16) = (SELECT [IDRRef] AS rcs_id FROM [Reference101] WHERE [Description] = 'какая-то категория' AND [Folder] =0x00);
Номенклатуры много, а мне нужна определённая категория. Далее пишем основу нашего таблично выражения:
`WITH q1 AS ( SELECT [IDRRef] ,[ParentIDRRef] ,[Description] ,[Folder] ,1 as generation_number FROM [Reference101] WHERE [ParentIDRRef] = @rcs_group UNION ALL SELECT [Reference101].[IDRRef] ,[Reference101].[ParentIDRRef] ,[Reference101].[Description] ,[Reference101].[Folder] ,generation_number + 1 FROM [Reference101] JOIN q1 ON [Reference101].[ParentIDRRef] =q1.[IDRRef] ) Данный рекурсивный подзапрос делает следующее: Вытаскивает айди строки, айди родителя, признак того, что данный элемент является папкой для всех элементов, у которых родителем выступает айди, который я сохранил в переменной, а именно папка железобетонных изделий. Этому списку я присваиваю номер 1 как первому поколению. Далее запрос проходит по таблице ещё раз, но уже вытаскивает те элементы, которые являлись дочерними для 1 первой выборки; новой подвыборке присваивается номер 2 и так цикл повторяется пока условие джойна истинно. В какой-то момент джойнить становится нечего уже, и рекурсия останавливается. Это база всей нашей конструкции. Теперь можно вытаскивать то, что нам надо. Отмечу, что использование этого подзапроса для извлечения самой номенклатуры – нецелесообразно. Для этого достаточно просто сделать выборку по условию WHERE [_Folder] = 0x01, то есть извлекаются все не папки, а конечные элементы. Собственно, так я и поступил. Теперь извлекаю родительские элементы. Для этого делаю подзапрос, где будут храниться родители единиц номенклатуры:
`,q2 AS ( SELECT DISTINCT([ParentIDRRef] ) FROM q1 WHERE [Folder] = 0x01)
Напомню, все эти подвыверты связаны с тем, что уровни иерархии в категоризации разные. Поэтому я не могу извлечь просто уровень максимум -1. То есть я вытаскиваю родителя для номенклатуры, которая идёт сразу в папке, и для номенклатуры, которая внутри 2-3-х папок.
Теперь извлекаем собственно родителей этих родителей:
`, q3 AS ( SELECT DISTINCT CASE WHEN generation_number < 4 THEN q1.[IDRRef] ELSE q1.[ParentIDRRef] END AS [ParentIDRRef] FROM q1 JOIN q2 ON q1.[IDRRef] = q2.[ParentIDRRef] WHERE q1.[Folder] = 0x00 )
Обратите внимание на логику: если номер поколения меньше того, который мне нужен, то я задаю родителю 4 поколения его же ID. То есть он как бы будет ссылаться на сам себя, до тех пор, пока я не доберусь до родителя, чтобы не было пропусков. То есть я реализую логику «категория-категория-категория-единица номенклатуры», о которой писал раньше. Почему так (это важно усвоить). Потому что я собираю таблицу категорий 3 уровня (в текущем подзапросе), а родители некоторых единиц номенклатуры сидят на 2-м или 1-м уровне. Будет пропуск на уровне модели данных, который недопустим.
Используя данную конструкцию и управляя номером поколения, можно собрать запросы, чтобы вытащить все нужные вам уровни категории номенклатуры. То есть просто копировать вставить q2 и q3, поменять уровни иерархии для сравнения и продолжить, пока не дойдёте до содержимого корневой папки. Просто напомню, что нужно будет делать копии запроса на этапах и вытаскивать нужные вам значения. «Для Power BI знание SQL не нужно», говорили они, когда я шёл на курсы)
Собственно, читатели, переваривайте это, а в след части я напишу кратенько, как при уже собранной модели выделить категории на те, которые от меня требует конечный заказчик.````
