入库出库表，求库存，这个sql如何写

入库出库表，求库存，这个sql怎么写？
把问题简化了说


--入库数（一个sql从入库表中按天分组汇总统计出来的）

 

日期 当天入库

2012-02-20 1000

2012-02-22 1500

 

--出库数据（一个sql从出库表中按天分组统计出来的）
 

日期 当天出库

2012-02-20 500

2012-02-21 300

2012-02-22 500

2012-02-24 400


 

--求下面的结果

 

日期 入库/出库类型 数量 库存

2012-02-20 入库 1000 500

2012-02-20 出库 500 500

2012-02-21 出库 300 200
 
2012-02-22 入库 1500 1200

2012-02-22 出库 500 1200

2012-02-24 出库 400 800 


[解决办法]

SQL code

USE TEMPDBGOIF OBJECT_ID('TB_IN') IS NOT NULL DROP TABLE TB_INIF OBJECT_ID('TB_OUT') IS NOT NULL DROP TABLE TB_OUTGOCREATE 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 ALLSELECT '2012-02-22', 1500INSERT INTO TB_OUTSELECT '2012-02-20', 500 UNION ALLSELECT '2012-02-21', 300 UNION ALLSELECT '2012-02-22', 500 UNION ALLSELECT '2012-02-24', 400GO;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_INUNION ALLSELECT OUT_DATE,0-TOTAL,'出库' AS TYPE FROM TB_OUT) T),MU2 AS (SELECT *,TOTAL AS [库存] FROM MU WHERE ROW=1UNION ALLSELECT T1.*,T2.[库存]+T1.TOTALFROM MU T1INNER JOIN MU2 T2 ON T1.ROW=T2.ROW+1)SELECT CHANGE_DATE AS [日期],TYPE AS [类型], ABS(TOTAL) AS [变更数量],[库存]FROM MU2/*日期    类型    变更数量    库存2012-02-20 00:00:00.000    入库    1000    10002012-02-20 00:00:00.000    出库    500    5002012-02-21 00:00:00.000    出库    300    2002012-02-22 00:00:00.000    入库    1500    17002012-02-22 00:00:00.000    出库    500    12002012-02-24 00:00:00.000    出库    400    800*/
[解决办法]
最好有一个递增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 allselect '2012-02-22', 1500insert into @t2select '2012-02-20', 500 union allselect '2012-02-21', 300 union allselect '2012-02-22', 500 union allselect '2012-02-24', 400select identity(int,1,1) as id,* into #t from(select D,Qty In_Qty,0 Out_Qty from @t1union allselect D,0,Qty from @t2) torder by D,In_Qty descselect 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 id<=t.id) as '库存'from #t as tdrop table #t/*日期                      入库/出库类型 数量                     库存----------------------- ------- ---------------------- ----------------------2012-02-20 00:00:00.000 入库      1000                   10002012-02-20 00:00:00.000 出库      500                    5002012-02-21 00:00:00.000 出库      300                    2002012-02-22 00:00:00.000 入库      1500                   17002012-02-22 00:00:00.000 出库      500                    12002012-02-24 00:00:00.000 出库      400                    800(6 行受影响)*/
[解决办法]
再看了一下,原来LZ要按天来算???那变一下

SQL code
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 id<=(select max(id) from #t where d=t.d)) as '库存'from #t as t/*日期                      入库/出库类型 数量                     库存----------------------- ------- ---------------------- ----------------------2012-02-20 00:00:00.000 入库      1000                   5002012-02-20 00:00:00.000 出库      500                    5002012-02-21 00:00:00.000 出库      300                    2002012-02-22 00:00:00.000 入库      1500                   12002012-02-22 00:00:00.000 出库      500                    12002012-02-24 00:00:00.000 出库      400                    800(6 行受影响)*/ 
 
[解决办法]
SQL code
--余下那一列明天给你解决，瞌睡了。use tempdbgocreate table in_INVS(    iid int,    idates datetime,    iquantity int)create table out_INVS(    uid int,    udates datetime,    uquantity int)insert into in_INVSselect 1,'2012-02-20',1000 union allselect 2,'2012-02-22',1500insert into out_INVSselect 1,'2012-02-20',500 union allselect 2,'2012-02-21',300 union allselect 3,'2012-02-22',500 union allselect 4,'2012-02-24',400 select * from in_INVSselect * from out_INVS--------------------------------------------------create procedure tempINVSasbegin    create table #3    (        idates datetime,        IN_OUT_TYPE nvarchar(10),        quantity int,        trueQuantity int    )    insert into #3(idates,IN_OUT_TYPE,quantity,trueQuantity)    select idates,'入库',iquantity,0    from in_INVS    insert into #3(idates,IN_OUT_TYPE,quantity,trueQuantity)    select udates,'出库',uquantity,0    from out_INVS    select * from #3end
[解决办法]
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 id<=t.id) as '库存'
from #t as t

drop table #t