sql - 加入查询结果

Question

我有一个问题，我有一张表用于保存传入记录，另一张用于保存传出记录，我需要做的是按 partNumber 对这些记录进行分组并获得单个输出。

InTable

PartNo Qty Date
A       1   1/1/13
A       5   1/1/13
B       2   1/1/13

OutTable

PartNo Qty Date
A       2  1/1/13
B       1  1/1/13
C       3  1/1/13


Result Needed

Date 1/1/13

PartNo In Out Total
A      6   2    4
B      2   1    1
C      0   3    -3  

我已经尝试过类似的方法，但由于总和(数量)，它会产生总计，但它无法以任何其他方式工作。

select a.PartNo, sum(b.inQty) as inQty,sum(c.outQty) as outQty, sum(b.inQty)-sum(c.outQty) as total  from 
  (Select PartNo FROM InTable
  where date= '01-01-2013'
  group by PartNo
  union
  Select PartNo  FROM OutTable
  where date= '01-01-2013'
  group by PartNo) A
  cross join
  (  
  SELECT PartNo,SUM(Qty) inQty FROM InTable
  where date= '01-01-2013'
  group by PartNo
  )B
  cross join
  ( 
  SELECT PartNo,SUM(Qty) outQty FROM OutTable
  where date= '01-01-2013'
  group by PartNo
  )c
  group by a.PartNo

我尝试加入三个查询，每个查询都会产生一些有用的结果，但问题是当我尝试加入它们时，查询会产生类似的结果

PartNo inQty outQty total 
A       8       6     2
B       8       6     2
C       8       6     2

有什么建议吗？谢谢。

Answer 1

对两个派生表使用完全外部联接:

SELECT 
    COALESCE(inTab.PartNo, outTab.PartNo) AS PartNo,
    COALESCE(inQty, 0),
    COALESCE(outQty, 0),
    COALESCE(inQty, 0) -  COALESCE(outQty, 0) AS total
FROM 
 (
   SELECT PartNo, SUM(Qty) AS inQty 
   FROM InTable
   WHERE DATE= '01-01-2013'
   GROUP BY PartNo
 ) InTab
FULL JOIN
 (
   SELECT PartNo, SUM(Qty) AS outQty 
   FROM OutTable
   WHERE DATE= '01-01-2013'
   GROUP BY PartNo
 ) OutTab
ON inTab.Partno = outTab.PartNo