Присвоить "Идентификатор группы" рекурсивному cte на основе WHERE ... IN ... в cte

ProgramBox

Присвоить «Идентификатор группы» рекурсивному cte на основе WHERE … IN … в cte

Post author:admin
Запись опубликована:31 января, 2023
Post category:Вопросы по программированию

#sql #sql-server

#sql #sql-сервер

Вопрос:

Я пытаюсь извлечь информацию из множества таблиц. Некоторые из них включают информацию о закупках на основе записей, содержащих записи обслуживания. У меня есть записи обслуживания (wiir), которые существуют на определенных физических единицах. В некоторых случаях для обслуживания было приобретено много деталей (спецификаций).

Я извлекаю информацию о wiir из одной таблицы, где WiirId равен PK, а bomlineid равен FK, дату закупки из другой таблицы через промежуточную таблицу и пытаюсь сгруппировать их по WIIRId, найдя самую раннюю информацию о закупках. Если есть способ идентифицировать группы информации, поступающие из cte, это было бы фантастически. Я открыт для любых предложений.

 USE emms_srm
-- Declare variables to use to pull data
DECLARE @Start AS datetime, @End AS datetime
SET @Start = '5/1/2014'
SET @End = '5/31/2014'

--Make the table variable to store the sorting list into for this report.
DECLARE @SortList AS TABLE
(
WiirId int NOT NULL
,BomLineId int NOT NULL
)
-- Make table variable to hold the group by list with MIN sc info
DECLARE @GroupList AS TABLE
(
WiirId int NULL
,WiirBomLineId int NULL
,GroupBomLineId int NOT NULL
,SRMSCId int NULL
,SRMSCLineId int NULL
,SRMSCDate datetime NULL
--  ,GroupId int NOT NULL
)
;
--Build list of WIIRs and BomLines for this report.
INSERT INTO @SortList
SELECT DISTINCT vw.WiirId
,vw.BomLineId

FROM dbo.vw_Wiirs AS vw
WHERE (vw.IsComplete = 'True') AND (vw.IsArchived = 'False') AND (IsDeleted = 'False')
AND (vw.CloseoutDate BETWEEN @Start AND @End)
AND (vw.WiirTypeId = 1) -- New Build (In-House or Make)
AND (vw.WiirSubTypeId IN (1,3,4,7,8,23))
AND (vw.PartNumber NOT LIKE '%R')
AND (vw.PartNumber NOT LIKE '%RFM')
AND (vw.PartNumber NOT LIKE '%L')
AND (vw.PartNumber NOT LIKE '%P')
 ORDER BY vw.WiirId;

--Build list of all children for bomlines from @SortList
WITH prep_cte1(BomLineId, ParentBomLineId, BomId, PartId, Title, Description/*, GroupId*/)
AS ( --Anchor member definition
SELECT bl.BomLineId
    ,bl.ParentBomLineId
    ,bl.BomId
    ,bl.PartId
    ,bl.Title
    ,REPLACE(REPLACE(bl.Description,CHAR(10),''),CHAR(13),'') AS 'Description'
    --NEED to assign a group id based on @SortList

FROM dbo.BomLines AS bl

WHERE bl.BomLineId IN (SELECT BomLineId FROM @SortList)
UNION ALL
-- Recursive member definition
SELECT bl.BomLineId
    ,bl.ParentBomLineId
    ,bl.BomId
    ,bl.PartId
    ,bl.Title
    ,REPLACE(REPLACE(bl.Description,CHAR(10),''),CHAR(13),'')
    --NEED to assign a group id based on @SortList

FROM dbo.BomLines AS bl
INNER JOIN prep_cte1 AS p
ON bl.ParentBomLineId = p.BomLineId
)
INSERT INTO @GroupList
--Statement that executes the CTE
SELECT sl.WiirId
,sl.BomLineId
,pc1.BomLineId
,r.SRMSCId
,r.SRMSCLineId
,MIN(r.SRMSCDate)
--  ,pc1.GroupId

FROM prep_cte1 AS pc1
LEFT JOIN dbo.ShoppingLineItems AS sli
ON pc1.BomLineId = sli.BomLineId
LEFT JOIN dbo.Requisitions AS r
ON sli.RequisitionId = r.RequisitionId
LEFT JOIN @SortList AS sl
ON pc1.BomLineId = sl.BomLineId

GROUP BY /*pc1.GroupId,*/ sl.WiirId, sl.BomLineId, pc1.BomLineId, r.SRMSCId, r.SRMSCLineId

ORDER BY sl.WiirId;

SELECT *
FROM @GroupList
--should return the 118 rows from @SortList with the SRMSC information based on the group by

Дополнение: я пытаюсь создать пример в SQLFiddle, но я использую его впервые. Что касается существующего вопроса, я надеюсь, что «идентификатор группы», который я пытаюсь создать, будет находиться внутри cte. В основном @SortList возвращает 118 строк, каждая с уникальным идентификатором WiirId и уникальным идентификатором BomLineId. Таблица BomLines также имеет уникальные значения BomLineId, но также содержит самоссылающееся поле «ParentBomLineId». Предполагается, что cte будет извлекать все уровни дочерних элементов в dbo.BomLines, но мне нужно иметь @SortList .WiirId или @SortList.BomLineId присваиваются всем дочерним элементам, поэтому я могу группировать по одному из этих значений, поэтому я знаю, что все дочерние элементы принадлежат этому WiirId или WiirBomLineId. В настоящее время CTE возвращает более 2500 строк, но только исходные 118 имеют ненулевое значение в @SortList .WiirId или @SortList.BomLineId

Как только у нас будет более 2500 строк с groupId, я затем извлекаю информацию о закупках из двух таблиц (связанных через две разные таблицы), где я извлекаю самую раннюю корзину покупок из одной таблицы и последний заказ на покупку, полученный из другой, в определенных группах WIIR. Таким образом, каждая из исходных 118 строк должна сообщать о выходе с указанием MIN(SRMSCDate) и MAX (FlashDate) для группы строк спецификации, которая находится под ней (из 2500 , полученных CTE).

1. Отправил ответ, но думаю, это было не то, что вы искали. Что такое группировка groupId, точно? Что нужно, чтобы он идентифицировал, который еще не идентифицирован? Например, вы хотите уровень рекурсии в cte или что-то в этом роде?

2. Если возможно опубликовать некоторые входные и ожидаемые выходные данные либо по вопросу, либо по sqlfiddle.com , это будет фантастически!

3. Я добавил некоторое содержимое для ясности. Приношу свои извинения за задержку между сообщениями.

Ответ №1:

В код для выбора идентификатора группы были внесены следующие изменения.

1. Объединения с @SortList были введены в prep_cte1, чтобы гарантировать, что выбран только идентификатор BomLineId в @SortList .

2.In последняя инструкция INSERT-SELECT sl.WiirId '-' sl.BomLineId выбирается в качестве идентификатора группы, который однозначно идентифицирует каждую группу.

Пересмотренный код:

 USE emms_srm
-- Declare variables to use to pull data
DECLARE @Start AS datetime, @End AS datetime
SET @Start = '5/1/2014'
SET @End = '5/31/2014'

--Make the table variable to store the sorting list into for this report.
DECLARE @SortList AS TABLE
(
WiirId int NOT NULL
,BomLineId int NOT NULL
)
-- Make table variable to hold the group by list with MIN sc info
DECLARE @GroupList AS TABLE
(
WiirId int NULL
,WiirBomLineId int NULL
,GroupBomLineId int NOT NULL
,SRMSCId int NULL
,SRMSCLineId int NULL
,SRMSCDate datetime NULL
--  ,GroupId int NOT NULL
)
;
--Build list of WIIRs and BomLines for this report.
INSERT INTO @SortList
SELECT DISTINCT vw.WiirId
,vw.BomLineId
FROM dbo.vw_Wiirs AS vw
WHERE (vw.IsComplete = 'True') AND (vw.IsArchived = 'False') AND (IsDeleted = 'False')
AND (vw.CloseoutDate BETWEEN @Start AND @End)
AND (vw.WiirTypeId = 1) -- New Build (In-House or Make)
AND (vw.WiirSubTypeId IN (1,3,4,7,8,23))
AND (vw.PartNumber NOT LIKE '%R')
AND (vw.PartNumber NOT LIKE '%RFM')
AND (vw.PartNumber NOT LIKE '%L')
AND (vw.PartNumber NOT LIKE '%P')
ORDER BY vw.WiirId;

--Build list of all children for bomlines from @SortList
WITH prep_cte1(BomLineId, ParentBomLineId, BomId, PartId, Title, Description/*, GroupId*/)
AS ( --Anchor member definition
SELECT bl.BomLineId
    ,bl.ParentBomLineId
    ,bl.BomId
    ,bl.PartId
    ,bl.Title
    ,REPLACE(REPLACE(bl.Description,CHAR(10),''),CHAR(13),'') AS 'Description'
FROM dbo.BomLines AS bl
INNER JOIN @SortList sl ON bl.BomLineId = sl.BomLineId
UNION ALL
-- Recursive member definition
SELECT bl.BomLineId
    ,bl.ParentBomLineId
    ,bl.BomId
    ,bl.PartId
    ,bl.Title
    ,REPLACE(REPLACE(bl.Description,CHAR(10),''),CHAR(13),'')
    --NEED to assign a group id based on @SortList

FROM dbo.BomLines AS bl
INNER JOIN prep_cte1 AS p
ON bl.ParentBomLineId = p.BomLineId
INNER JOIN @SortList sl ON bl.BomLineId = sl.BomLineId
)
INSERT INTO @GroupList
--Statement that executes the CTE
SELECT sl.WiirId
,sl.BomLineId
,pc1.BomLineId
,r.SRMSCId
,r.SRMSCLineId
,MIN(r.SRMSCDate)
--  ,pc1.GroupId
,sl.WiirId   '-'   sl.BomLineId GroupId
FROM prep_cte1 AS pc1
LEFT JOIN dbo.ShoppingLineItems AS sli
ON pc1.BomLineId = sli.BomLineId
LEFT JOIN dbo.Requisitions AS r
ON sli.RequisitionId = r.RequisitionId
LEFT JOIN @SortList AS sl
ON pc1.BomLineId = sl.BomLineId

GROUP BY /*pc1.GroupId,*/ sl.WiirId, sl.BomLineId, pc1.BomLineId, r.SRMSCId, r.SRMSCLineId, sl.WiirId   '-'   sl.BomLineId

ORDER BY sl.WiirId;

SELECT *
FROM @GroupList
--should return the 118 rows from @SortList with the SRMSC information based on the group by

1. Привет. С точки зрения производительности, я думаю, что ОБЪЕДИНЕНИЕ лучше, чем WHERE…IN , так что я согласен с вами в этом. Но я не думаю, что OP не смог понять, как объединить два значения в конечном запросе, оба значения уже были доступны до ваших изменений, и ОБЪЕДИНЕНИЕ в cte не повлияло на это. Я думаю, что OP ищет значение, которое может быть вычислено только в cte, для каждой итерации. В любом случае, мы не будем знать наверняка, пока он не прокомментирует. Подождите и посмотрите, я думаю.

2. @VBlades Да, я согласен. Нам нужно четко знать, как должен выглядеть groupId.

Ответ №2:

Если я вас правильно понимаю, вы хотите иметь идентификатор для уровня рекурсии. Если это так, это сработает для вас:

 --Build list of all children for bomlines from @SortList
WITH prep_cte1(BomLineId, ParentBomLineId, BomId, PartId, Title, Description, GroupId)
AS ( --Anchor member definition
    SELECT bl.BomLineId
        ,bl.ParentBomLineId
        ,bl.BomId
        ,bl.PartId
        ,bl.Title
        ,REPLACE(REPLACE(bl.Description,CHAR(10),''),CHAR(13),'') AS 'Description'
        ,1 AS GroupID

    FROM dbo.BomLines AS bl INNER JOIN @SortList sl
         ON bi.BomLineID = sl.BomLineID

    UNION ALL

    -- Recursive member definition
    SELECT bl.BomLineId
        ,bl.ParentBomLineId
        ,bl.BomId
        ,bl.PartId
        ,bl.Title
        ,REPLACE(REPLACE(bl.Description,CHAR(10),''),CHAR(13),'')
        ,p.GroupId   1

    FROM dbo.BomLines AS bl
    INNER JOIN prep_cte1 AS p
    ON bl.ParentBomLineId = p.BomLineId
)

Просто введите значение 1 в первой части cte и добавляйте к нему 1 на каждой итерации.