sql语句 求助

以下是以 left join 查出来的。如果未出过库 用left查出来是nuLL 再将null 替换成 零就成了（剩余数量=入库数量-出库数量，未出过库出库数量就是零）
select a.material_class,a.material_name,a.material_brand,a.material_type,
(a.material_num-(isnull(b.materialout_num,0))) as 剩余数量
from (
select material_class,material_name,material_brand,material_type,
sum(material_num) as material_num --入库数量
from MM_materialadd
group by material_class,material_name,material_brand,material_type
) a
left join
(select materialout_class,materialout_name,materialout_brand,materialout_type,
sum(materialout_num) as materialout_num --出库数量
from MM_materialout
group by materialout_class,materialout_name,materialout_brand,materialout_type
) b on a.material_class=b.materialout_class
and a.material_name=b.materialout_name
and a.material_brand = b.materialout_brand
and a.material_type = b.materialout_type
希望对你有帮助。

用LEFT JOIN 以入库表为主表。
有入库才能出库，但有入库不一定出库，如果你用＝相当与INNER JOIN，了

select a.material_class,
a.material_name,
a.material_brand,
a.material_type,
(a.material_num - b.materialout_num) as 剩余数量
from (select material_class,
material_name,
material_brand,
material_type,
sum(material_num) as material_num
from MM_materialadd
group by material_class,
material_name,
material_brand,
material_type) a,
(select materialout_class,
materialout_name,
materialout_brand,
materialout_type,
sum(materialout_num) as materialout_num
from MM_materialout
group by materialout_class,
materialout_name,
materialout_brand,
materialout_type) b
where a.material_class = b.materialout_class(+)
and a.material_name = b.materialout_name(+)
and a.material_brand = b.materialout_brand(+)
and a.material_type = b.materialout_type(+)

看着眼晕，弄个图出来。

你用的是内联接，所以有一方没有资料的数据是不会出来的，你可以用左连接或右连接来实现。
如select * from a left join b on a.col1 = b.col1 and a.col2 = b.col2 这样如果a中有而b中没有的资料也会出来。

介绍sql语句如何使用函数