如何使用sql查询查出库存呢

Question

CREATE TABLE LC_IO (
ID INTEGER PRIMARY KEY,--出库库行主键
GOODS_ID INTEGER  ,--物料ID
IN_ID INTEGER  ,   --物料来源
OUT_ID INTEGER  ,    --物料送到
NUM NUMBER(10,2) ,--数量
IO_KIND NUMBER(2)   --1 入库   2 出库  0未定义
);

INSERT INTO "LC_IO"("ID", "GOODS_ID", "IN_ID", "OUT_ID", "NUM", "UNIT", "IN_HANDER", "OUT_HANDER", "IO_DESC", "IO_STATUS", "IO_DATE", "IO_SN", "IO_CRI", "IO_CTIME", "IO_MODI", "IO_MTIME") VALUES ('1', '468', '5002', '5000', '100', NULL, NULL, NULL, NULL, '1', TO_DATE('2021-08-01 01:50:34', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, TO_DATE('2021-08-01 01:50:38', 'SYYYY-MM-DD HH24:MI:SS'), NULL, TO_DATE('2021-08-01 01:50:38', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "LC_IO"("ID", "GOODS_ID", "IN_ID", "OUT_ID", "NUM", "UNIT", "IN_HANDER", "OUT_HANDER", "IO_DESC", "IO_STATUS", "IO_DATE", "IO_SN", "IO_CRI", "IO_CTIME", "IO_MODI", "IO_MTIME") VALUES ('2', '468', '5000', '57', '50', NULL, NULL, NULL, NULL, '1', TO_DATE('2021-08-01 01:50:34', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, TO_DATE('2021-08-01 02:07:35', 'SYYYY-MM-DD HH24:MI:SS'), NULL, TO_DATE('2021-08-01 02:07:35', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "LC_IO"("ID", "GOODS_ID", "IN_ID", "OUT_ID", "NUM", "UNIT", "IN_HANDER", "OUT_HANDER", "IO_DESC", "IO_STATUS", "IO_DATE", "IO_SN", "IO_CRI", "IO_CTIME", "IO_MODI", "IO_MTIME") VALUES ('3', '442', '5000', '56', '11', NULL, NULL, NULL, NULL, '1', TO_DATE('2021-08-01 01:50:34', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, TO_DATE('2021-10-30 11:05:38', 'SYYYY-MM-DD HH24:MI:SS'), NULL, TO_DATE('2021-10-30 11:05:38', 'SYYYY-MM-DD HH24:MI:SS'));
INSERT INTO "LC_IO"("ID", "GOODS_ID", "IN_ID", "OUT_ID", "NUM", "UNIT", "IN_HANDER", "OUT_HANDER", "IO_DESC", "IO_STATUS", "IO_DATE", "IO_SN", "IO_CRI", "IO_CTIME", "IO_MODI", "IO_MTIME") VALUES ('4', '442', '5000', '56', '10', NULL, NULL, NULL, NULL, '1', TO_DATE('2021-08-01 01:50:34', 'SYYYY-MM-DD HH24:MI:SS'), NULL, NULL, TO_DATE('2021-10-30 11:05:38', 'SYYYY-MM-DD HH24:MI:SS'), NULL, TO_DATE('2021-10-30 11:05:38', 'SYYYY-MM-DD HH24:MI:SS'));

数据库表是
【其实我就是吧出库与入库都放在一个表里面了】

要求的结果是

上图的ID与仓库ID要调换一下 我示意图错了

我的错误语句以及结果是

SELECT
    T2.GOODS_ID,
    T2.IN_ID 仓库,
    ( T2.INSUM - T1.OUTSUM ) 库存 
FROM
    ( SELECT GOODS_ID, in_ID, sum（ num） INSUM FROM LC_IO GROUP BY goods_id, in_ID ORDER BY GOODS_ID ) T2 --入库
    INNER JOIN
        ( SELECT GOODS_ID, OUT_ID, sum（ num） OUTSUM FROM LC_IO GROUP BY goods_id, OUT_ID ORDER BY GOODS_ID ) T1 --出库
    ON T1.GOODS_ID = T2.GOODS_ID

Answer 1

select goods_id,sum(num) from LC_IO group by goods_id