Справка по запросу: суммирование родительских/дочерних элементов

[Ред. Примечание. Относится к этому вопросу SO; не редактировал вопрос, потому что проблема коренным образом изменилась, а предыдущие вопросы/ответы по-прежнему актуальны]

Нужен совет, как лучше создать вид. Текущий запрос из моей предыдущей проблемы теперь работает, но не учитывает идею родительских/дочерних элементов.

Соответствующая структура данных

+---------+      +---------+
|WORKORDER|      |WPLABOR  |
|---------|      |---------|
|WONUM    |+---->|WONUM    |
|PARENT   |  +   |LABORHRS |
|ISTASK   |  |   |RATE     |
|...      |  |   +---------+
|         |  |
+---------+  |   +---------+
             |   |WPITEM   |
             |   |---------|
             +-->|WONUM    |
             |   |ITEMQTY  |
             |   |UNITCOST |
             |   +---------+
             |
             |   +----------------+
             |   |LONGDESCRIPTION |
             |   |----------------|
             +-->|LDKEY           |
                 |LDTEXT          |
                 +----------------+

Цель

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

Поэтому мне нужно представление, которое возвращает:

  • Стандартная информация об этом рабочем задании (идентифицированная WONUM) — описание, местоположение и т. д.
  • Общее количество трудозатрат (SUM of LABOHRRS) для каждого из дочерних рабочих заданий
  • Общая стоимость рабочей силы (сумма LABOHRRS*RATE) от WPLABOR Для каждого из дочерних рабочих заданий
  • Общая стоимость товара (СУММА ITEMQTY*UNITCOST) из WPITEM Для каждого из его дочерних рабочих заданий

Рабочий заказ дочерней задачи — это рабочий заказ, где родитель = WONUM первого рабочего заказа и ISTASK=1.

Использовать:

Я хотел бы иметь возможность вызывать это следующим образом:

SELECT * from [ViewName] where wonum = '123abc';

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

Текущий запрос

SELECT WORKORDER.WONUM      ,
       WORKORDER.ACTLABHRS  ,
       WORKORDER.LOCATION   ,
       WORKORDER.STATUS     ,
       WORKORDER.WO7        , -- Requester
       WORKORDER.WO8        , -- Extension
       WORKORDER.WO9        , -- Location
       WORKORDER.LEADCRAFT  ,
       WORKORDER.WO11       , -- Extension
       WORKORDER.GLACCOUNT  ,
       WORKORDER.WO10       , -- Contact
       WORKORDER.DESCRIPTION, -- Short description
       WORKORDER.WO6        , -- Plant rearrangement (YORN / boolean value)
       WORKORDER.ISTASK     ,
       WORKORDER.PARENT     ,
       LABOR.TOTALLABORHRS  ,
       LABOR.LABORCOST      ,
       ITEM.ITEMCOST        ,
       DESCRIPTION.LDTEXT
FROM   MAXIMO.WORKORDER
       LEFT JOIN
              ( SELECT  WPLABOR.WONUM                                    ,
                       SUM(WPLABOR.LABORHRS * WPLABOR.RATE) AS LABORCOST ,
                       SUM(WPLABOR.LABORHRS)                AS TOTALLABORHRS
              FROM     MAXIMO.WPLABOR
              GROUP BY WONUM
              )
              LABOR
       ON     WORKORDER.WONUM = LABOR.WONUM
       LEFT JOIN
              ( SELECT  WPITEM.WONUM ,
                       SUM(WPITEM.ITEMQTY * WPITEM.UNITCOST) AS ITEMCOST
              FROM     MAXIMO.WPITEM
              GROUP BY WONUM
              )
              ITEM
       ON     WORKORDER.WONUM = ITEM.WONUM
       LEFT JOIN
              ( SELECT LONGDESCRIPTION.LDKEY,
                      LONGDESCRIPTION.LDTEXT
              FROM    MAXIMO.LONGDESCRIPTION
              WHERE   LONGDESCRIPTION.LDOWNERTABLE='WORKORDER'
              AND     LONGDESCRIPTION.LDOWNERCOL  = 'DESCRIPTION'
              )
              DESCRIPTION
       ON     WORKORDER.WONUM = CAST(DESCRIPTION.LDKEY AS VARCHAR(22)) HERE WORKORDER.ISTASK = 1;

Проблемы с текущим запросом

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


sql view oracle sql-scripts
person SeanKilleen    schedule 09.11.2011    source источник
comment
Connect by Prior пройдёт за вас по родительскому дочернему дереву, а наборы группировки могут суммировать итоги. Потребуется приличное количество усилий, чтобы написать логику.   -  person xQbert    schedule 09.11.2011
comment
Я никогда не работал с Connect by Prior. не могли бы вы предоставить ответ, в котором вы излагаете концепции логического/псевдо-sql? Я думаю, что если бы я увидел основную идею шагов, которые мне нужно предпринять, у меня было бы гораздо больше возможностей. Очень признателен!   -  person SeanKilleen    schedule 10.11.2011
comment
Вопрос было бы легче читать, если бы использовались корреляционные имена и избегали смеси ALLUPPERCASE и CamelCase. Кроме того: я не вижу, как таблица LONGDESCRIPTION должна относиться к любой из трех других таблиц.   -  person wildplasser    schedule 10.11.2011

Ответы (1)


arrow_upward
0
arrow_downward

Я придумал запрос, который разрешает ситуацию и работает немного быстрее, чем «подключение по предыдущему» методу (3 секунды по сравнению с ~ 90 секундами).

Чтобы упростить задачу, я выделил стандартную информацию о заказе на работу в отдельное представление (в любом случае я хотел бы использовать ее повторно).

Запрос, который я получил (ниже), похоже, помогает:

CREATE OR replace VIEW r_wo_costsummariesbyparent 
AS 
  SELECT workorder.parent, 
         Nvl(SUM(wosummary.totallaborhoursforwo), 0) AS totallaborhoursforparent, 
         Nvl(SUM(wosummary.totallaborcostforwo), 0)  AS totallaborcostforparent, 
         Nvl(SUM(wosummary.totalitemcostforwo), 0)   AS totalitemcostforparent 
  FROM   maximo.workorder 
         inner join (SELECT workorder.wonum, 
                            Nvl(SUM(wplabor.laborhrs), 0) AS totallaborhoursforwo, 
                            Nvl(SUM(wplabor.laborhrs * wplabor.rate), 0) AS totallaborcostforwo, 
                            Nvl(SUM(wpitem.itemqty * wpitem.unitcost), 0) AS totalitemcostforwo 
                     FROM   maximo.workorder 
                            inner join maximo.wplabor 
                              ON ( workorder.wonum = wplabor.wonum ) 
                            inner join maximo.wpitem 
                              ON ( workorder.wonum = wpitem.wonum ) 
                     WHERE  workorder.istask = 1 
                     GROUP  BY workorder.wonum) wosummary 
           ON workorder.wonum = wosummary.wonum 
  GROUP  BY workorder.parent;

Я использую его следующим образом:

Select * from r_wo_costsummariesbyparent where parent = '123abc';
person SeanKilleen    schedule 10.11.2011
comment
Может ли WONUM существовать как первичный ключ без каких-либо дочерних ключей (нет записей с родительским полем, совпадающим с wonum? Если да, хотите ли вы увидеть родителей в результате с 0 часами и 0 затратами на элемент? Если да, то вы выполняете внутреннее соединение на внешний выбор должен быть левым соединением. Во-вторых, ваш дизайн ТРЕБУЕТ наличия как записи wplabor, так и записи wpItem, иначе вы не получите никакой записи. ВСЕ ваши рабочие заказы ИМЕЮТ как wplabor, так и wpitems, и будут ли они продолжать делать так?Если нет, то вам нужно использовать левые соединения на внутреннем выборе. - person xQbert; 10.11.2011