属性相同的多个记录

Question

我正在试图总结库存零件的数量。该部件有一个原始零件号和一个备用零件号。所述备用部件号可用作原始零件号的替换。我希望能够把这个零件号和备用零件号的总数加在一起，在原来的零件号下面。

partno | qty
------------
  a1      2
  a2      3

partno | altpartno | qty
------------------------
   a1       b1        2
   a2       b2        2

结果：

partno | qty
-------------
   a1     4
   a2     5

以下是我目前的尝试：

    WITH
    on_order as (
    SELECT
    partno,Count(*) 'On Order'
    FROM amos.od_header oh join amos.od_detail od on(od.orderno_i=oh.orderno_i)
    where oh.state='O' and oh.order_type='P'
    GROUP BY partno
    ),
    on_hand as (
    SELECT p.partno 'Partno',SUM(s.qty) 'Qty On Hand'
    FROM amos.part_alternate p WITH (NOLOCK)
           left outer join amos.store s WITH (NOLOCK)  on  p.partno=s.partno or p.altpartno=s.partno
           left outer join amos.location l WITH (NOLOCK) on  s.locationno_i=l.locationno_i
           left outer join amos.location_restriction lr WITH (NOLOCK) on  lr.location_restriction=l.location_restriction
    WHERE l.location_restriction!='1' --Where Location is not restricted
    GROUP BY p.partno
    ),
alternate_on_hand AS (
SELECT p.altpartno,SUM(s.qty) 'AQty On Hand'
    FROM amos.part_alternate p WITH (NOLOCK)
           left outer join amos.store s WITH (NOLOCK)  on  p.altpartno=s.partno or p.altpartno=s.partno
           left outer join amos.location l WITH (NOLOCK) on  s.locationno_i=l.locationno_i
           left outer join amos.location_restriction lr WITH (NOLOCK) on  lr.location_restriction=l.location_restriction
    WHERE l.location_restriction!='1' --Where Location is not restricted
    GROUP BY p.altpartno)

    SELECT oh.Partno,oh.[Qty On Hand],oo.[On Order], aoh.altpartno, aoh.[AQty On Hand]
    FROM on_hand oh join on_order oo on(oo.partno=oh.Partno) join alternate_on_hand aoh on(aoh.altpartno=oh.partno)

我的问题是，我不知道如何正确地把所有的部分加起来。我的输出如下：

partno | qty | altparnto | Aqty
-------------------------------
  A1      2      B1         1
  A1      2      B2         2

当前使用。

谢谢!

Answer 1

我认为你可以通过一个简单的查询。下面是一种使用union all和group by的方法

select partno, sum(qty)
from ((select partno, qty
       from table1
      ) union all
      (select partno, qty
       from table2
      )
     ) t
group by partno;

您的查询引用了许多在查询中没有提到的表和列。然而，这一想法也应在那里奏效。