SQL我有两张表，分别是"入库表"和“出库表”,出库表有一个字段“出库编号”是主键，

这两个表里有一个相同字段是"商品编号"，我需要匹配出每个商品编码的出库时间-入库时间，生成新列"时长"，因为同商品编码存在多次出入库情况，匹配结果存在很多重复值（这也是必须的），我需要查询每次出库的在库时长，所以要保留每个“出库编号”时长最小的值，删除重复数据，请各位大神指导一下，再次拜谢

select a1.入库数量 ,b1.出库数量,(a1.入库数量 -b1.出库数量) 库存数量 from (select a.产品规格,a.产品名称,sum(a.产品数量) 入库数量 from 入库表 a group by
a.产品规格,a.产品名称
) a1,
(select b.产品规格,b.产品名称,sum(b.产品数量) 出库数量 from 入库表 b
group by
b.产品规格,b.产品名称
) b1

where a1.产品规格=b1.产品规格 and a1.产品名称=b1.产品名称

把问题简化了说 --入库数（一个sql从入库表中按天分组汇总统计出来的） 日期 当天入库 --出库数据（一个sql从出库表中按天分组统计出来的） 日期 当天出库 --求下面的结果 日期 入库/出库类型 数量 库存 ------解决方案--------------------------------------------------------sql code use tempdb go if object_id('tb_in') is not null drop table tb_in if object_id('tb_out') is not null drop table tb_out go create table tb_in( in_date datetime ,total int ) create table tb_out( out_date datetime ,total int ) insert into tb_in select '2012-02-20', 1000 union all select '2012-02-22', 1500 insert into tb_out select '2012-02-20', 500 union all select '2012-02-21', 300 union all select '2012-02-22', 500 union all select '2012-02-24', 400 go ;with mu as ( select *,row_number() over(order by change_date,case when type='入库' then 1 else 2 end) as row from ( select in_date as change_date,total,'入库' as type from tb_in union all select out_date,0-total,'出库' as type from tb_out ) t ) ,mu2 as ( select *,total as [库存] from mu where row=1 union all select t1.*,t2.[库存]+t1.total from mu t1 inner join mu2 t2 on t1.row=t2.row+1 ) select change_date as [日期],type as [类型], abs(total) as [变更数量],[库存] from mu2 ------解决方案-------------------------------------------------------- 最好有一个递增id,sql2005以上版本可以用row_number()产生id,下面是用临时表,好理解点 sql code declare @t1 table (d datetime,qty float) declare @t2 table (d datetime,qty float) insert into @t1 select '2012-02-20', 1000 union all select '2012-02-22', 1500 insert into @t2 select '2012-02-20', 500 union all select '2012-02-21', 300 union all select '2012-02-22', 500 union all select '2012-02-24', 400 select identity(int,1,1) as id,* into #t from (select d,qty in_qty,0 out_qty from @t1 union all select d,0,qty from @t2) t order by d,in_qty desc select d as '日期', case when in_qty>0 then '入库' else '出库' end as '入库/出库类型', case when in_qty>0 then in_qty else out_qty end as '数量', (select sum(in_qty)-sum(out_qty) from #t where id0 then '入库' else '出库' end as '入库/出库类型', case when in_qty>0 then in_qty else out_qty end as '数量', (select sum(in_qty)-sum(out_qty) from #t where id

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息