代码之家 › 专栏 › 技术社区 › Harun24hr

将表1和表2中的数量相加,然后减去以查看库存(Access SQL)

access-vba ms-access sql

0

Harun24hr · 技术社区 · 6 年前

我有两张桌子 Access 数据库两张桌子的结构都是一样的。第一个表用于事务输入,第二个表用于事务输出。我想得到全部 Transaction In 总共 Transaction Out 为每个项目,然后显示 Stock .见下面我的表格结构和数据截图。

我正在使用下面的查询,这给了我错误的输出。

SELECT TABLE1.ID, Sum(TABLE1.qty) AS TR_In, Sum(TABLE2.qty) AS TR_Out, Sum(TABLE1.qty)-Sum(TABLE2.qty) AS Stock
          FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.ID = TABLE2.ID
GROUP BY TABLE1.ID;

我的预期输出如下

表1数据

身份证件	描述	数量
RM001	项目1	3.
RM001	项目1	3.
RM002	项目2	2.
RM003	项目3	1.
RM003	项目3	1.

表2数据

身份证件	描述	数量
RM001	项目1	1.
RM001	项目1	1.
RM001	项目1	1.
RM002	项目2	1.

1 回复 | 直到 4 年前

1

2

Gustav 6 年前

使用联合查询并对以下结果进行求和:

Select
    ID,
    Sum(TRIn) As TR_In,
    Sum(TROut) As TR_Out,
    Sum(Total) As Stock
From

(Select
    ID,
    Description,
    Qty As TRIn,
    0 As TROut,
    Qty As Total
From
    table1
Order By ID

Union All

Select
    ID,
    Description,
    0 As TRIn,
    Qty As TROut,
    -Qty As Total
From
    table2)

Group By
    ID

输出:

2

1

Gordon Linoff 6 年前

目前,如果在聚合前先加入,则在加入前将其翻转以聚合两个表,以避免重复计算匹配ID:

SELECT agg1.id, agg1.TR_In, agg2.TR_Out, 
       (agg1.TR_In - NZ(agg2.TR_Out, 0)) AS Stock
FROM (SELECT TABLE1.ID, SUM(TABLE1.qty) AS TR_In 
      FROM TABLE1
      GROUP BY TABLE1.ID
     ) as agg1 LEFT JOIN
     (SELECT TABLE2.ID, SUM(TABLE2.qty) AS TR_Out 
      FROM TABLE2
      GROUP BY TABLE2.ID
     ) as agg2
     ON agg1.ID = agg2.ID

3

0

Jonathan Willcock 6 年前

Parfait的答案中有几个无关的逗号(在AS TR_in AS TR_Out之后)以及缺少的IIF。

总的来说,查询应该如下所示:

SELECT agg1.id, agg1.TR_In, agg2.TR_Out, 
       (agg1.TR_In - IIF(agg2.TR_Out IS NULL, 0, agg2.TR_Out)) AS Stock
FROM
 (SELECT TABLE1.ID, SUM(TABLE1.qty) AS TR_In
  FROM TABLE1
  GROUP BY TABLE1.ID) agg1
LEFT JOIN
 (SELECT TABLE2.ID, SUM(TABLE2.qty) AS TR_Out
  FROM TABLE2
  GROUP BY TABLE2.ID) agg2
ON agg1.ID = agg2.ID

如果需要,也可以对第二列重复IIF,这样就得到0而不是null。