我有俩个库,入库和出库,
入库的字段有:ruku_num(入库编号),code(序号:与出库的序号是一样),name(入库物品的名称),
ruku_time(入库的时间),ruku_shuliang(入库的数量),danwei(数量单位)出库的字段有:chuku_num(出库编号),code(序号:与出库的序号是一样),name(出库物品的名称),
chuku_time(出库的时间),chuku_shuliang(出库的数量),danwei(数量单位)我想求一条sql语句,表头如下:序号(code),物品名称(name),入库数量(ruku_shuliang),出库数量(shuku_shuliang),剩余数量(shengyu=入库数量-出库数量),
数量单位(danwei)在查询上述记录的时候有个条件:就是必须在一段时间内,例如在2000年到2008年之间的。(我这个入出库是相同的序号可能有多次入库和多次出库)希望各位能回答我的问题,谢谢,我随时在线,还有我的qq是963598233,谢谢回答
入库的字段有:ruku_num(入库编号),code(序号:与出库的序号是一样),name(入库物品的名称),
ruku_time(入库的时间),ruku_shuliang(入库的数量),danwei(数量单位)出库的字段有:chuku_num(出库编号),code(序号:与出库的序号是一样),name(出库物品的名称),
chuku_time(出库的时间),chuku_shuliang(出库的数量),danwei(数量单位)我想求一条sql语句,表头如下:序号(code),物品名称(name),入库数量(ruku_shuliang),出库数量(shuku_shuliang),剩余数量(shengyu=入库数量-出库数量),
数量单位(danwei)在查询上述记录的时候有个条件:就是必须在一段时间内,例如在2000年到2008年之间的。(我这个入出库是相同的序号可能有多次入库和多次出库)希望各位能回答我的问题,谢谢,我随时在线,还有我的qq是963598233,谢谢回答
sum(ruku_shuliang-shuku_shuliang) as bal,danwei
from (
select code,name,ruku_shuliang, 0 as shuku_shuliang union all
select code,name,0, shuku_shuliang as shuku_shuliang
) t
group by code,name,danwei
(select code , name , sum(ruku_shuliang) ruku_shuliang from 入库 group by code , name) m left join
(select code , name , sum(ruku_shuliang) chuku_shuliang from 出库 group by code , name) n on m.code = n.code
select code,name,sum(ruku_shuliang) as ruku_shuliang,sum(shuku_shuliang) as shuku_shuliang,
sum(ruku_shuliang-shuku_shuliang) as bal,danwei
from (
select code,name,danwei,ruku_shuliang, 0 as shuku_shuliang union all
select code,name,danwei,0, shuku_shuliang
) t
group by code,name,danwei
(select code , name , danwei , sum(ruku_shuliang) ruku_shuliang from 入库 group by code , name, danwei) m left join
(select code , name , danwei , sum(ruku_shuliang) chuku_shuliang from 出库 group by code , name, danwei) n on m.code = n.code
sum(ruku_shuliang-shuku_shuliang) as bal,danwei
from (
select code,name,danwei,ruku_time,ruku_shuliang, 0 as shuku_shuliang union all
select code,name,danwei,chuku_time,0, shuku_shuliang
) t
where year(ruku_time) between 2000 and 2008
group by code,name,danwei
select a.code 序号 ,a.name 物品名称 ,a.rksl 入库数量 ,b.cksl 出库数量 ,a.rksl -b.cksl as 剩余数据 from (
select code,name,sum(入库数量) as rksl from 入库 where ruku_time between '2008-12-01'
and '2008-12-02' group by code,name) a left join
(
select code,name,sum(入库数量) as cksl from 出库 where cuku_time between '2008-12-01'
and '2008-12-02' group by code,name) b
on a.code=b.code
from
(select code , name , danwei , sum(ruku_shuliang) ruku_shuliang from 入库 group by code , name, danwei) m
left join
(select code , name , danwei , sum(ruku_shuliang) chuku_shuliang from 出库 group by code , name, danwei) n
on m.code = n.code
select code,name,sum(ruku_shuliang) as ruku_shuliang,sum(shuku_shuliang) as shuku_shuliang,
sum(ruku_shuliang-shuku_shuliang) as bal,danwei
from (
select code,name,danwei,ruku_time,ruku_shuliang, 0 as shuku_shuliang from 入库 union all
select code,name,danwei,chuku_time,0, shuku_shuliang from 出库
) t
where year(ruku_time) between 2000 and 2008
group by code,name,danwei
sum(ruku_shuliang-shuku_shuliang) as bal,danwei
from (
select code,name,danwei,ruku_time,ruku_shuliang, 0 as shuku_shuliang from 入库表 union all
select code,name,danwei,chuku_time,0, shuku_shuliang from 出库表
) t
where year(ruku_time) between 2000 and 2008
group by code,name,danwei