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
/*
日期    类型    变更数量    库存
2012-02-20 00:00:00.000    入库    1000    1000
2012-02-20 00:00:00.000    出库    500    500
2012-02-21 00:00:00.000    出库    300    200
2012-02-22 00:00:00.000    入库    1500    1700
2012-02-22 00:00:00.000    出库    500    1200
2012-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 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

/*
日期                      入库/出库类型 数量                     库存
----------------------- ------- ---------------------- ----------------------
2012-02-20 00:00:00.000 入库      1000                   1000
2012-02-20 00:00:00.000 出库      500                    500
2012-02-21 00:00:00.000 出库      300                    200
2012-02-22 00:00:00.000 入库      1500                   1700
2012-02-22 00:00:00.000 出库      500                    1200
2012-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