当前位置：首页 → 问答吧 → 求总数量的sql语句

求总数量的sql语句

时间：2011-11-25

来源：互联网

三个表
物料表:id,名称,型号...
入库表：id,...入库数量，入库日期...
出库表：id，...出库数量，出库日期...

现在要得到这样一个表：名称，型号，总入库数量，总出库数量，库存数量

我这样写只得到第一行的数据
SQL code

select Mat_name,Mat_mode,sum( MIS_qty ) as mistotal,sum(MO_qty) as mototal
from material m,materialinstorage mis,materialoutstock mo
where m.Mat_id=mis.Mat_id and mis.MIS_id=mo.MIS_id

这个应该怎么写呀？求高手指点

作者: mengdiQian 发布时间: 2011-11-25

SQL code


select Mat_name,Mat_mode,sum( MIS_qty ) as mistotal,sum(MO_qty) as mototal
from material m,materialinstorage mis,materialoutstock mo
where m.Mat_id=mis.Mat_id and mis.MIS_id=mo.MIS_id
group by Mat_name,Mat_mode

作者: AcHerat 发布时间: 2011-11-25

SQL code


select t1.名称,
       t1.型号,
       总入库数量 = isnull((select sum(入库数量) from 入库表 t2 where t2.id = t1.id),0),
       总出库数量 = isnull((select sum(出库数量) from 出库表 t3 where t3.id = t1.id),0),
       库存数量 = isnull((select sum(入库数量) from 入库表 t2 where t2.id = t1.id),0) - isnull((select sum(出库数量) from 出库表 t3 where t3.id = t1.id),0)
from 物料表 t1

作者: dawugui 发布时间: 2011-11-25

select Mat_name,Mat_mode,
mistotal=(select sum(MIS_qty )from materialinstorage where mat_id=m.mat_id),
mototal=(select sum(MO_qty) from materialoutstock where MIS_id=Mat_id)
from material m

作者: liw125008 发布时间: 2011-11-25

SQL code




select Mat_name,Mat_mode,b.mistotal,c.mototal from material a 
left join 
(select mistotal = sum(MIS_qty),mat_id from materialinstorage group by mat_id) b on a.id = b.mat_id
left join 
(select mototal = sum(MO_qty),mat_id from materialoutstock  group by mat_id)c on a.id = c.mat_id

作者: yibey 发布时间: 2011-11-25

group by Mat_name,Mat_mode

用到聚合函数，显示列需要加上分组

作者: roy_88 发布时间: 2011-11-25

引用 1 楼 acherat 的回复:
SQL code

select Mat_name,Mat_mode,sum( MIS_qty ) as mistotal,sum(MO_qty) as mototal
from material m,materialinstorage mis,materialoutstock mo
where m.Mat_id=mis.Mat_id and mis.MIS_id=mo.MIS_id
……

直接group by不对
我入库一次10个，出库4次，每次一个，它显示的就是总入库40个，总出库4个

作者: mengdiQian 发布时间: 2011-11-25