尝试从包含以下格式的买卖文件的txt文件中获取数据。 我想按项目分组并互相减去。
我进行了三个查询,例如totalin和totalout以及库存,但是当我从in减去时,缺少了一些未售出的物品。
这是数据表
+------+---------+--------+
| TYPE | ITEM | AMOUNT |
+------+---------+--------+
| BUY | APPLE | 100 |
| BUY | ORANGE | 100 |
| BUY | APPLE | 200 |
| BUY | ORANGE | 200 |
| SELL | APPLE | 50 |
| SELL | APPLE | 50 |
| SELL | ORANGE | 100 |
| SELL | ORANGE | 100 |
| BUY | COCONUT | 50 |
| SELL | BANANE | 30 |
+------+---------+--------+
我想要这个输出
+---------+--------+
| ITEM | AMOUNT |
+---------+--------+
| APPLE | 200 |
| BANANE | -30 |
| COCONUT | 50 |
| ORANGE | 100 |
+---------+--------+
我对想要的结果进行了3次查询,但不幸的是我被卡住了。
这是我的查询
第1个查询,共:
SELECT DATA.TYPE,DATA.ITEM,Sum(DATA.AMOUNT) AS TOTALIN
FROM DATA
GROUP BY DATA.TYPE,DATA.ITEM
HAVING (((DATA.TYPE)="BUY"));
查询2总计:
SELECT DATA.TYPE,Sum(DATA.AMOUNT) AS TOTALOUT
FROM DATA
GROUP BY DATA.TYPE,DATA.ITEM
HAVING (((DATA.TYPE)="SELL"));
查询3库存:
SELECT DATA.ITEM,[BUY]![TOTAL_IN]-[SELL]![TOTAL_OUT] AS STOK
FROM (DATA INNER JOIN BUY ON DATA.ITEM = BUY.ITEM) INNER JOIN SELL ON DATA.ITEM = SELL.ITEM
GROUP BY DATA.ITEM,[BUY]![TOTAL_IN]-[SELL]![TOTAL_OUT];
如何进行查询,将其余部分显示为库存。
非常感谢。