T-SQL для суммирования общего значения вместо повторного присоединения к таблице несколько раз

Я искал пример такого вопроса, я прошу благодати, если на него был дан ответ (я думал, что это будет, но мне трудно найти значимые результаты с терминами, которые я искал.)

Я работаю на заводе-изготовителе, где при каждой производственной операции детали присваивается новый серийный номер. В таблице базы данных, с которой мне приходится работать, серийный номер записан в поле Container, а предыдущий серийный номер детали записан в поле From_Container.

Я пытаюсь SUM колонку Extended_Cost на частях, над которыми нам пришлось повторно выполнять операции.

Вот пример данных из tbl_Container:

Container   From_Container  Extended_Cost   Part_Key Operation
10                       9  10               PN_100  60
9                        8  10               PN_100  50
8                        7  10               PN_100  40
7                        6  10               PN_100  30
6                        5  10               PN_100  20
5                        4  10               PN_100  50
4                        3  10               PN_100  40
3                        2  10               PN_100  30
2                        1  10               PN_100  20
1                      100  10               PN_100  10

В этом примере SUM, как я ожидаю, будет равно 40, потому что операции 20, 30, 40 и 50 были переделаны и стоили 10 долларов каждая.

До сих пор мне удавалось это делать, присоединяя таблицу к самой себе 10 раз, используя псевдонимы следующим образом:

      LEFT OUTER JOIN   tbl_Container  AS  FCP_1
                  ON    tbl_Container.From_Container = FCP_1.Container
                  AND   FCP_1.Operation       <= tbl_Container.Operation
                  AND   tbl_Container.Part_Key       = FCP_1.Part_Key

А затем с помощью SUM сложите вместе Extended_Cost полей. Однако я нарушаю принцип DRY, и должен быть лучший способ.

Заранее спасибо за вашу помощь,

Me


loops sql-server while-loop tsql hierarchical
person Python_Learner_DK    schedule 18.01.2018    source источник
comment
Другими словами, вы хотите выполнить иерархический запрос. SQL Server имеет тип hierarchyid для представления иерархий. Или вы можете использовать рекурсивные CTE. Нет причин использовать циклы.   -  person Panagiotis Kanavos    schedule 18.01.2018
comment
@PanagiotisKanavos Возможно, вы правы, я не знаком с иерархическими запросами, чтобы, запрашивая цикл, я мог попросить неправильное решение.   -  person Python_Learner_DK    schedule 18.01.2018
comment
То, что вы описываете, является хорошо известной проблемой обработки данных, Bill of Materials (BOM). Google для T-SQL BOM для различных решений. Если материал/компонент используется только в одном продукте/объекте/чем-то еще верхнего уровня, это, по сути, иерархический запрос, который можно ускорить с помощью hierarchyid. hierarchyid — это, по сути, путь от текущего элемента к вершине, поэтому он может представлять только одну иерархию. Если компонент используется в нескольких продуктах, вам нужен либо рекурсивный CTE, либо другая таблица с возможными путями (сложно)   -  person Panagiotis Kanavos    schedule 18.01.2018
comment
Проверьте этот возможный дубликат Рекурсивный запрос для списка материалов. Также погуглите T-SQL BOM. Это ни в коем случае не простая/решаемая проблема, и есть много методов, которые могут помочь. Рекурсия может быть слишком глубокой, или могут быть циклы, которые могут привести к бесконечной рекурсии. Возможно, вы захотите рассчитать более сложный агрегат (цена с скидками за объем). Графические решения также могут помочь.   -  person Panagiotis Kanavos    schedule 18.01.2018
comment
@PanagiotisKanavos это тот же вопрос, который я нашел, когда гуглил то, что вы рекомендовали. Может быть, это дубликат, я не могу сказать на первый взгляд, но, похоже, это действительно так. Должен ли я закрыть или удалить это?   -  person Python_Learner_DK    schedule 18.01.2018
comment
@PanagiotisKanavos, я попробовал код по ссылке, которую вы разместили, и не могу заставить его работать. Я считаю, что есть сходство, но мне не нужен счетчик для того, сколько слоев в глубину проходят эти круги.   -  person Python_Learner_DK    schedule 18.01.2018
comment
@SDS Почему последняя запись столбца from_container имеет значение 100? Я бы ожидал, что это значение будет NULL, если бы это была первая операция. Кроме того, соединение в вашем примере ссылается на столбец part_key, но не видно его в данных примера. Вы не против добавить это?   -  person tarheel    schedule 19.01.2018
comment
@tarheel изменил Part_No на Part_Key, ошибка с моей стороны. Кроме того, from_container относится к серийным номерам, поэтому 100 просто указывает на то, что входная часть для первого шага также имеет серийный номер. Я мог выбрать любой номер. Стоит отметить, что Container_No генерируется по мере того, как детали производятся на нашем предприятии. В моем простом примере числа идут одно за другим, но на самом деле мы производим сотни деталей в час, поэтому числа никогда не идут одно за другим.   -  person Python_Learner_DK    schedule 19.01.2018
comment
@SDS Понятно, что значения Container не являются последовательными в фактических данных. Если бы те же 10 записей выборочных данных находились в середине 1000 других записей, что я мог бы использовать, чтобы определить, с чего началось создание детали? Я бы предположил, что могу найти, где закончилось создание части, когда нет записи, которая имеет значение From_Container, совпадающее со значением Container (в данном случае значение Container равно 10). Не возражаете ли вы добавить образцы данных для второй создаваемой части? Кроме того, какая у вас версия SQL Server? (2008/2012/2014/и т.д..)   -  person tarheel    schedule 20.01.2018

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете попробовать этот запрос.

;WITH CTE AS
(
    SELECT TOP 1 *, I = 0 FROM tbl_Container C ORDER BY Container 
    UNION ALL
    SELECT T.*, I = I + 1 FROM CTE 
        INNER JOIN tbl_Container T 
            ON CTE.Container = T.From_Container 
            AND CTE.Part_Key = T.Part_Key
)
SELECT Part_Key, SUM(T1.Extended_Cost) Sum_Extended_Cost FROM CTE T1
WHERE 
    EXISTS( SELECT * FROM 
            CTE T2 WHERE 
            T1.Operation = T2.Operation 
            AND T1.I > T2.I ) 
GROUP BY Part_Key

Результат:

Part_Key   Sum_Extended_Cost
---------- -----------------
PN_100     40
person Serkan Arslan    schedule 20.01.2018
comment
Я медленно принимал, пытаясь проверить и убедиться, что это работает, прежде чем делать это. Работа была сумасшедшей, взволнован, чтобы заставить ее работать. Извинения. - person Python_Learner_DK; 23.01.2018