查询帮助：父/子项目总计

Question

[埃德。注意：与这个SO问题相关；没有编辑问题，因为问题已经从根本上发生了变化，之前的问题/答案仍然有效]

寻求有关如何最好地创建视图的建议。我之前的问题中的当前查询现在可以工作，但没有考虑父/子项目的想法。

相关数据结构

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

目标

通俗地说，我让某人输入工作订单编号，并提取包含工作订单信息的报告，以及所有子工作订单（也称为任务工作订单）的劳动力和材料成本摘要

因此，我需要一个返回的视图：

• 有关该工作订单的标准信息（由 WONUM 标识）——描述、位置等
• 每个子任务工作订单的总工时（劳动力总和）
• 总劳动力来自 WPLABOR 的成本（LABORRHRS*RATE 之和） 对于其每个子任务工作订单
• 来自 WPITEM 的总项目成本（ITEMQTY*UNITCOST 之和） 对于其每个子任务工作订单

子任务工作订单 是一个工作订单其中父级 = 第一个工作订单的 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;

的当前查询问题

因为它不考虑子任务工单，所以它不返回任何人工或物料记录（没有任何记录与父级直接关联），因此人工和物料总计始终为空。

Answer 1

我提出了一个查询来解决这种情况，并且比“通过先前的连接”方法快一点（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';