SQL Server 2012: как получить до 5 уровней иерархии данных из исходной таблицы в таблицу производных продуктов
У меня есть таблица с 3 столбцами:
Id (identity),
Sku_Parent (varchar(10)),
Sku_Child (varchar(10))
Я хотел бы получить помощь в написании рекурсивного запроса, чтобы получить до 5 уровней родительско-дочерних отношений.
Когда новый производный продукт вставляется, новый производный продукт также (в конечном итоге) может использоваться в качестве родительского. Я хочу иметь возможность отслеживать историю каждого нового производного продукта, на 5 уровней ниже и находить главного родителя. Я надеюсь, что это четкое объяснение.
Вот пример данных, которые в настоящее время находятся в моей таблице:
7038 N0179890 N0180323
7039 N0180323 N0180328
7040 N0180323 N0180329
7041 N0180323 N0180330
7042 N0180323 N0180331
7043 N0180323 N0180332
7044 N0180323 N0180333
7045 N0180323 N0180334
Я нашел примеры, но те, которые я нашел, предполагают, что есть только одна родительская запись и имеет нулевое значение.
Это ссылка для получения копии данных таблицы:
https://drive.google.com/open?id=1hP7kRQsl_8YzEu4dK9Z8J91y-2Gvh7Qi
Пожалуйста посоветуй. Большое спасибо.
ОБНОВИТЬ
Я вижу, что sku P55645 использовался для создания 9 производных продуктов, и в этом случае ни один из производных продуктов не стал родительским. Это совершенно нормально.
Я просто думаю, что было бы легче читать, если вместо того, чтобы отображать 9 строк, каждый из скусов помещается в одну строку, начиная с верхнего уровня.
As it currently is:
P0055645 P0098245 P0055645, P0098245
P0055645 P0110959 P0055645, P0110959
P0055645 P0110960 P0055645, P0110960
P0055645 P0110961 P0055645, P0110961
P0055645 P0110962 P0055645, P0110962
P0055645 P0110963 P0055645, P0110963
P0055645 P0110964 P0055645, P0110964
P0055645 P0110965 P0055645, P0110965
P0055645 P0157714 P0055645, P0157714
I think this might read better:
P0055645, P0098245, P0110959, P0110960, P0110961, P0110962, P0110963, P0110964, P0110965, P0157714
This is also current output that I am having a hard time following:
Parent Derived Hierarchy
P0172879 P0178192 P0172879, P0178192
P0178192 P0178206 P0172879, P0178192, P0178206 - until this point i see that the relationship of parent child and then the chil becoming a parent is well illustrated
P0178206 P0178219 P0172879, P0178192, P0178206, P0178219 -- but then P0178206 becomes the parent of 6 new skus. Do you think this looks good, or should the output be like the first example I described?
P0178206 P0178220 P0172879, P0178192, P0178206, P0178220
P0178206 P0178221 P0172879, P0178192, P0178206, P0178221
P0178206 P0178222 P0172879, P0178192, P0178206, P0178222
P0178206 P0178223 P0172879, P0178192, P0178206, P0178223
P0178206 P0178224 P0172879, P0178192, P0178206, P0178224
ВТОРОЕ ОБНОВЛЕНИЕ
P0170926 P0170928 P0170926, P0170928 -- if sku load on form is P0170926, then the query will load a record-set of 2 records
P0170928 P0170929 P0170926, P0170928, P0170929 -- if user loads on form P0170928 then it will load 3 records including P0170926
P0170932 P0174069 P0170932, P0174069 -- here if user loads P0170932 the record set will only contain one record, shouldn't all the records up to P0174075 be also included?
P0170932 P0174070 P0170932, P0174070
P0170932 P0174071 P0170932, P0174071
P0170932 P0174072 P0170932, P0174072
P0170932 P0174073 P0170932, P0174073
P0170932 P0174074 P0170932, P0174074
P0170932 P0174075 P0170932, P0174075
1 ответ
Как то так (адаптировано из этого ответа):
WITH Skus as
(
SELECT
Sku_Parent Sku
, CAST(NULL AS varchar(10)) Parent
, CAST(Sku_Parent AS varchar(max)) Hierarchy
FROM YourTable P
WHERE Sku_Parent NOT IN (SELECT Sku_Child FROM YourTable)
UNION ALL
SELECT
P.Sku_Child
, P.Sku_Parent
, M.Hierarchy + ', ' + CAST(P.Sku_Child AS varchar(max))
FROM
YourTable P
JOIN Skus M ON M.Sku = P.Sku_Parent
)
SELECT DISTINCT
Sku
, Parent
, Hierarchy
FROM Skus
Схема таблицы:
Адаптировано к вашей схеме таблицы:
WITH [Skus]
AS
(
SELECT
[P].[SKU_original] [Sku]
, CAST(NULL AS VARCHAR(12)) [Parent]
, CAST([P].[SKU_original] AS VARCHAR(MAX)) [Hierarchy]
FROM [dbo].[production_derived_products] [P]
WHERE [P].[SKU_original] NOT IN
(
SELECT [SKU_derived]
FROM [dbo].[production_derived_products]
)
UNION ALL
SELECT
[P].[SKU_derived]
, [P].[SKU_original]
, [M].[Hierarchy] + ', ' + CAST([P].[SKU_derived] AS VARCHAR(MAX))
FROM
[dbo].[production_derived_products] [P]
JOIN [Skus] [M] ON [M].[Sku] = [P].[SKU_original]
)
SELECT DISTINCT
[Skus].[Sku]
, [Skus].[Parent]
, [Skus].[Hierarchy]
FROM [Skus]
;
ОБНОВИТЬ
Что касается этого:
P0170932 P0174069 P0170932, P0174069 -- here if user loads P0170932 the record set will only contain one record, shouldn't all the records up to P0174075 be also included?
P0170932 P0174070 P0170932, P0174070
P0170932 P0174071 P0170932, P0174071
P0170932 P0174072 P0170932, P0174072
P0170932 P0174073 P0170932, P0174073
P0170932 P0174074 P0170932, P0174074
P0170932 P0174075 P0170932, P0174075
Причина, по которой он не будет загружать один список всех записей, заключается в том, что этот столбец предназначен для иерархии, т. Е. Предложенный вами вывод будет описывать P0174070 как дочерний элемент P0174069, тогда как на самом деле это дочерний элемент P0170932.
Во всяком случае, я много играл с этим, и я думаю, что следующее даст вам практически все, что вам нужно (дайте мне знать, если нет).
Я использовал WHERE предложение в конце, так что вы можете запустить это и получить быстрое демо, но вы можете удалить это, чтобы увидеть все.
WITH Skus as
(
SELECT
SKU_original Sku
, CAST(NULL AS varchar(12)) Parent
, SKU_original AbsoluteHierarchicalParent
, CAST(SKU_original AS varchar(max)) Hierarchy
, 0 [Level]
FROM production_derived_products P
WHERE SKU_original NOT IN (SELECT SKU_derived FROM production_derived_products)
UNION ALL
SELECT
P.SKU_derived
, P.SKU_original
, M.AbsoluteHierarchicalParent
, M.Hierarchy + ', ' + CAST(P.SKU_derived AS varchar(max))
, M.[Level] + 1
FROM
production_derived_products P
JOIN Skus M ON M.Sku = P.SKU_original
)
,
SkusDistinct
AS
(
SELECT DISTINCT
Sku
, Parent
, AbsoluteHierarchicalParent
, Hierarchy
, [Level]
FROM Skus
)
,
SkuHierarchies
AS
(
SELECT
Sku
, Sku StartSku
, 0 [Level]
FROM
(
SELECT SKU_original Sku
FROM production_derived_products
UNION
SELECT SKU_derived
FROM production_derived_products
) AllSkus
UNION ALL
SELECT
P.SKU_derived
, M.StartSku
, M.[Level] + 1
FROM
production_derived_products P
JOIN SkuHierarchies M ON M.Sku = P.SKU_original
)
,
SkuHierarchiesDistinct
AS
(
SELECT DISTINCT
Sku
, StartSku
, [Level]
FROM SkuHierarchies
)
SELECT
SD1.Sku
, SD1.Parent
, SD1.AbsoluteHierarchicalParent
,
STUFF(
(
SELECT ', ' + SD2.Sku
FROM SkusDistinct SD2
WHERE SD2.Parent = SD1.Sku
ORDER BY SD2.Sku
FOR XML PATH ('')
)
, 1, 2, '') ImmediateChildren
, SD1.Hierarchy Heritage
, SD1.[Level]
,
STUFF(
(
SELECT ', ' + H.Sku + ' (' + CAST(H.[Level] AS varchar) + ')'
FROM SkuHierarchiesDistinct H
WHERE H.StartSku = SD1.Sku
ORDER BY
H.[Level]
, H.Sku
FOR XML PATH ('')
)
, 1, 2, '') SkuHierarchyByLevel
, (SELECT MAX([Level]) + 1 FROM SkuHierarchiesDistinct WHERE StartSku = SD1.Sku) SkuHierarchyTotalLevels
,
STUFF(
(
SELECT ', ' + SD2.Sku + ' (' + CAST(SD2.[Level] AS varchar) + ')'
FROM SkusDistinct SD2
WHERE SD2.AbsoluteHierarchicalParent = SD1.AbsoluteHierarchicalParent
ORDER BY
SD2.[Level]
, SD2.Sku
FOR XML PATH ('')
)
, 1, 2, '') FullHierarchyByLevel
, MAX(SD1.[Level]) OVER (PARTITION BY AbsoluteHierarchicalParent) + 1 FullHierarchyTotalLevels
FROM SkusDistinct SD1
WHERE AbsoluteHierarchicalParent = 'P0172879'