如果 id_item 是独一无二的 in_used 最多有一排 out_used 那你只需要一个 LEFT 加入并 COALESCE() :

select i.id_item, 
       i.qnt - coalesce(o.qnt, 0) used_Stock
from in_used i left join out_used o
on i.id_item = o.id_item

如果有更多行，请同时使用聚合：

select i.id_item, 
       sum(i.qnt) - coalesce(sum(o.qnt), 0) used_Stock
from in_used i left join out_used o
on i.id_item = o.id_item
group by i.id_item

听起来你想要这样的东西：

select item, sum(inc)
from ((select item, in_used as inc
       from items_in
      ) union all
      (select item, - out_used as inc
       from items_out
      )
     ) ii
group by item;

我不确定这两个表中的每一行是代表一个项目，还是有一个计数器。如果有一个计数器，那么应该用它来代替 1 / -1 .

在这种情况下，我们知道我们在所有情况下都有id\项用于连接结果，并且没有一个是空的：

SELECT I.id_item,
    SUM(I.qnt) - SUM(O.qnt) AS used_Stock
FROM in_used AS I
JOIN out_used AS O ON I.id_item = O.id_item
GROUP BY id_item

但对于一般结果，您可以尝试以下查询：

SELECT (CASE
        WHEN I.id_item IS NOT NULL THEN I.id_item
        ELSE O.id_item
    END) AS id_item,
    SUM(I.qnt) - SUM(O.qnt) AS used_Stock
FROM in_used AS I
JOIN out_used AS O ON I.id_item = O.id_item
GROUP BY id_item

希望这对你有用！

如果每个id有多个记录，则可以使用sum（），如果两个表上都没有记录，则必须使用full join，否则可以使用left join

Select
    id_item , coalesce(sum(in_used.qnt),0) - coalesce(sum(out_used.qnt),0) As used_Stock
From
    in_used FULL Join
    out_used On in_used.id_item = out_used.id_item
GROUP BY id_item