Question

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

Answer 1

把问题简化了说
--入库数（一个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 /* 日期 类型 变更数量 库存 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 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