现在有三个表,入库表,出库表,备件信息表,入库出库两表的数据相减得出库存,代码如下
select T_SYS_MATERIAL.MATERIAL_NAME,T_SYS_MATERIAL.MATERIAL_GUIGE,(sum(t_data_machinesideinto.intocount)-sum(t_data_machinesideout.outcount)) from t_data_machinesideinto,t_data_machinesideout,T_SYS_MATERIAL
where t_data_machinesideinto.FK_MATERIEL=t_data_machinesideout.FK_MATERIEL and t_data_machinesideinto.FK_MATERIEL=T_SYS_MATERIAL.MATERIALID
group by t_data_machinesideinto.FK_MATERIEL,T_SYS_MATERIAL.MATERIAL_NAME,T_SYS_MATERIAL.MATERIAL_GUIGE
现在想要加上时间限制,就是说统计在一段时间内,某个备件的原始库存是多少,入库多少,出库多少,结余多少,该怎么改代码》
select T_SYS_MATERIAL.MATERIAL_NAME,T_SYS_MATERIAL.MATERIAL_GUIGE,(sum(t_data_machinesideinto.intocount)-sum(t_data_machinesideout.outcount)) from t_data_machinesideinto,t_data_machinesideout,T_SYS_MATERIAL
where t_data_machinesideinto.FK_MATERIEL=t_data_machinesideout.FK_MATERIEL and t_data_machinesideinto.FK_MATERIEL=T_SYS_MATERIAL.MATERIALID
group by t_data_machinesideinto.FK_MATERIEL,T_SYS_MATERIAL.MATERIAL_NAME,T_SYS_MATERIAL.MATERIAL_GUIGE
现在想要加上时间限制,就是说统计在一段时间内,某个备件的原始库存是多少,入库多少,出库多少,结余多少,该怎么改代码》
select T_SYS_MATERIAL.MATERIAL_NAME,
T_SYS_MATERIAL.MATERIAL_GUIGE,
trunc(end_date)-trunc(start_date)-----加上起始时间,结束时间,对你的时间段进行分组
(sum(t_data_machinesideinto.intocount) -
sum(t_data_machinesideout.outcount))
from t_data_machinesideinto, t_data_machinesideout, T_SYS_MATERIAL
where t_data_machinesideinto.FK_MATERIEL =
t_data_machinesideout.FK_MATERIEL
and t_data_machinesideinto.FK_MATERIEL = T_SYS_MATERIAL.MATERIALID
group by t_data_machinesideinto.FK_MATERIEL,
T_SYS_MATERIAL.MATERIAL_NAME,
T_SYS_MATERIAL.MATERIAL_GUIGE,
trunc(end_date)-trunc(start_date)