- Foxtable(狐表) (http://www.foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://www.foxtable.com/bbs/list.asp?boardid=2)
---- 请教一句sq统计该如何写 (http://www.foxtable.com/bbs/dispbbs.asp?boardid=2&id=152387)
-- 发布时间：2020/7/11 10:11:00
-- 请教一句sq统计该如何写
如下图，一个表结构是这样的，如何能用一句sql，得到右边的统计结果
此主题相关图片如下：snipaste_2020-07-11_10-09-18.png
-- 发布时间：2020/7/11 10:21:00
--
select 物料编号,sum(入数量)-sum(出数量) as 库存 from (select 物料编号,isnull(数量,0) as 入数量,0 as 出数量 from {表A} where 类型=‘入库’ union all select 物料编号,0 as 入数量,isnull(数量,0) as 出数量 from {表A} where 类型=‘出库’) group by 物料编号
-- 发布时间：2020/7/11 10:30:00
--
以下是引用有点蓝在2020/7/11 10:21:00的发言：
select 物料编号,sum(入数量)-sum(出数量) as 库存 from (select 物料编号,isnull(数量,0) as 入数量,0 as 出数量 from {表A} where 类型=‘入库’ union all select 物料编号,0 as 入数量,isnull(数量,0) as 出数量 from {表A} where 类型=‘出库’) group by 物料编号
select 物料编号,sum(入数量)-sum(出数量) as 库存 from (select 物料编号,isnull(数量,0) as 入数量,0 as 出数量 from {表A} where 类型=‘入库’ union all select 物料编号,0 as 入数量,isnull(数量,0) as 出数量 from {表A} where 类型=‘出库’) group by 物料编号
报了个错误
此主题相关图片如下：微信截图_20200711102903.png
-- 发布时间：2020/7/11 10:39:00
--
select 物料编号,sum(入数量)-sum(出数量) as 库存 from (select 物料编号,IIF(数量 is null,0,数量) as 入数量,0 as 出数量 from {表A} where 类型=‘入库’ union all select 物料编号,0 as 入数量,IIF(数量 is null,0,数量) as 出数量 from {表A} where 类型=‘出库’) group by 物料编号