SELECT 库存组织, 物料编码, null as 系统结存,null as 未发数,null as 自由状态,数量 as 门店其它,null as 盘点总数
FROM 门店其他
union all
SELECT 库存组织, 物料编码, null as 系统结存,null as 未发数,null as 自由状态,null as 门店其它,
[盘点数量(件)]*(select 换算 from 物料信息 where 物料信息.物料编码=盘点入库.物料编码)+[盘点数量(瓶/盒)] as 盘点总数
FROM 盘点入库
union all
SELECT 库存组织, 物料编码, null as 系统结存,出库主数量 as 未发数,null as 自由状态,null as 门店其它,null as 盘点总数
FROM 未发数
union all
SELECT 库存组织, 物料编码, 系统结存,null as 未发数,null as 自由状态,null as 门店其它,null as 盘点总数
FROM 系统结存
UNION ALL SELECT 库存组织, 物料编码, null as 系统结存,null as 未发数,出库主数量 as 自由状态,null as 门店其它,null as 盘点总数
FROM 自由状态;
'最终结果查询
SELECT 物料汇总.库存组织, 物料信息.物料分类编码, 物料信息.物料分类名称, 物料汇总.物料编码, 物料信息.物料名称, Sum(物料汇总.系统结存) AS 系统结存之合计, Sum(物料汇总.未发数) AS 未发数之合计, Sum(物料汇总.自由状态) AS 自由状态之合计, Sum(物料汇总.门店其它) AS 门店其它之合计, 物料汇总.盘点总数
FROM 物料汇总 INNER JOIN 物料信息 ON 物料汇总.物料编码 = 物料信息.物料编码
GROUP BY 物料汇总.库存组织, 物料信息.物料分类编码, 物料信息.物料分类名称, 物料汇总.物料编码, 物料信息.物料名称, 物料汇总.盘点总数;