连接查询结果

Question

我有一个问题，我有一张表格，其中存储了进货记录和另一张表格存储了出货记录。我需要按照零件号对这些记录进行分组，并得到单个输出。

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  

我曾尝试过类似的方法，但由于sum(qty)的存在，它会影响总数，而其他方法则无法实现。

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

有什么建议吗？谢谢。

- Phra Tankian

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   

- dnoeth

---

先连接再分组将返回错误答案，例如PartNo为'A'的InTable有2行，OutTable有3行，则结果为2*3=6行。 - dnoeth

Answer 2

尝试使用UNION ALL代替FULL OUTER JOIN（演示链接）

;with cte as (
    select partNo, Qty inQty, 0 outQty, mydate
    from inTable
    union all
    select partNo, 0 inQty, qty outQty, mydate
    from outTable
)
select partNo, sum(inQty) inQty,
               sum(outQty) outQty,
               sum(inQty) - sum(outQty) total
from cte
where mydate = '01/01/2013'
group by partNo
order by partNo

结果

| PARTNO | INQTY | OUTQTY | TOTAL |
---------|-------|--------|-------|
|      A |     6 |      2 |     4 |
|      B |     2 |      1 |     1 |
|      C |     0 |      3 |    -3 |

- Kaf

Answer 3

使用全连接（Full Join）。

SELECT
  Coalesce(I.PartNo,O.PartNo) AS PartNo,
  IsNull(I.Qty,0) AS [In],
  IsNull(O.Qty,0) AS [Out],
  IsNull(I.Qty,0) - IsNull(O.Qty,0) AS [Total]
FROM
  (SELECT PartNo, Sum(Qty) AS Qty FROM InTable WHERE Date = '1/1/2013' GROUP BY PartNo) I
  FULL JOIN
  (SELECT PartNo, Sum(Qty) AS Qty FROM OutTable WHERE Date = '1/1/2013' GROUP BY PartNo) O ON I.PartNo = O.PartNo;

Sql Fiddle Example

的英译中，保留了HTML格式，不进行解释。

- Josh Jay

---

小心，当两个表中 PartNo 有多行时，它会返回错误答案。请使用派生表中的聚合函数检查我的答案。 - dnoeth

Answer 4

你需要使用 FULL JOIN 而不是 CROSS JOIN。你正在将 A 与从 B 和 C 返回的所有行连接起来。

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
  FULL JOIN
  (  
  SELECT PartNo,SUM(Qty) inQty FROM InTable
  where date= '01-01-2013'
  group by PartNo
  ) B ON b.partno = a.partno
  FULL JOIN
  ( 
  SELECT PartNo,SUM(Qty) outQty FROM OutTable
  where date= '01-01-2013'
  group by PartNo
  ) c ON c.partno = a.partno
  group by a.PartNo

编辑 由于您想要查看仅在intable或仅在outtable中的行，因此将LEFT JOIN更改为FULL JOIN。

- JayGee

---

全外连接不是更好吗？ - Cruncher

Answer 5

with part_cte(partno,date)as
(select partno,date from Intable
Union
Select partno,date from OutTable)

,intable_cte(partno,Qty,date)AS
(select Partno,sum(Qty)qty,Date from Intable Group By Partno,Date)

, outtable_cte(partno,Qty,date)AS
(select Partno,sum(Qty)qty,Date from outtable Group By Partno,Date)

select PC.partno,ISNULL(IC.QTY,0),ISNULL(OC.qty,0),PC.Date
From part_cte PC LEFT Join intable_cte IC on PC.partno=IC.partno and PC.date=IC.Date
LEFT Join outtable_cte OC on PC.partno=OC.partno and PC.date=OC.Date

- Sonam

Answer 6

试试这个：

select isNull(a.PartNo,b.PartNo) as PartNo,
       IsNull(sum(a.qty),0) as InAmt,
       IsNull(sum(b.qty),0) as OutAmt,
       IsNull(sum(a.qty),0)-IsNull(sum(b.qty),0) as Diff
from Intable a
full join OutTable b on a.partno=b.partNo
group by isNull(a.PartNo,b.PartNo) 

SQLFiddle: http://www.sqlfiddle.com/#!3/5d06d/8

SQLFiddle：http://www.sqlfiddle.com/#!3/5d06d/8

- Sparky

Answer 7

我没有看到派生表，所以这是我从Firebird移植过来的解决方案

select t.id as [PartNo],
       sum(T.In) as [IN],
       sum(t.Out) as [Out],
       sum(T.inc)-sum(t.decc) as "Total" 
from (
     select p.Partno as [id] ,p.id as [In],0    as [Out],p.Date as dt from PARTSIN p
     union
     select o.Partno as [id], 0    as [In],o.id as [Out,o].Date as dt from PARTSOUT o
) as t
where t.dt='1/1/2013'
group by t.id;

- danisius