引用他人数据：
--结存表
CREATE TABLE Stocks(Item varchar(10),Period int,Balance int)
INSERT Stocks SELECT 'aa',200501,100
UNION ALL SELECT 'cc',200501,100
--明细账数据
CREATE TABLE tb(
ID int IDENTITY PRIMARY KEY,
Item varchar(10), --产品编号
Quantity int, --交易数量
Flag bit, --交易标志,1代表入库,0代表出库,这样可以有效区分退货(负数)
Date datetime) --交易日期
INSERT tb SELECT 'aa',100,1,'2005-1-1'
UNION ALL SELECT 'aa',90 ,1,'2005-2-1'
UNION ALL SELECT 'aa',55 ,0,'2005-2-1'
UNION ALL SELECT 'aa',-10,1,'2005-2-2'
UNION ALL SELECT 'aa',-5 ,0,'2005-2-3'
UNION ALL SELECT 'aa',200,1,'2005-2-2'
UNION ALL SELECT 'aa',90 ,1,'2005-2-1'
UNION ALL SELECT 'aa',-10,1,'2005-3-2'
UNION ALL SELECT 'bb',95 ,1,'2005-2-2'
UNION ALL SELECT 'bb',65 ,0,'2005-2-3'
UNION ALL SELECT 'bb',-15,1,'2005-2-5'
UNION ALL SELECT 'bb',-20,0,'2005-2-5'
UNION ALL SELECT 'bb',100,1,'2005-2-7'
UNION ALL SELECT 'cc',100,1,'2005-1-7'
GO
==============说明=================
Item(品名) Date（年-月） Opening（本月期初） IN（本月入库） IN_Retrun（本月入库退回） OUT（本月出库） OUT_Return（本月出库退回） Balance（本月期末/本月库存）
=========大概结果如下===================
Item Date Opening IN IN_Retrun OUT OUT_Return Balance
---------- ---------------- -------------- ----------- ---------------- ----------- ------------------- -----------
aa 2005-02 225 200 10 0 5 420
bb 2005-02 0 195 15 65 20 135
cc 2005-02 100 0 0 0 0 100
aa 2005-03 420 0 10 0 0 410
------解决思路----------------------
SELECT Item,CONVERT(VARCHAR(7),Date,120)Date
,MIN(Opening)Opening
,ISNULL(SUM(CASE WHEN Flag>0 AND Quantity>=0 THEN Quantity END),0)[IN]
,ABS(ISNULL(SUM(CASE WHEN Flag>0 AND Quantity<0 THEN Quantity END),0))[IN_Retrun]
,ISNULL(SUM(CASE WHEN Flag<=0 AND Quantity>=0 THEN Quantity END),0)[OUT]
,ABS(ISNULL(SUM(CASE WHEN Flag<=0 AND Quantity<0 THEN Quantity END),0))[OUT_Return]
,MAX(Balance)Balance
FROM (
SELECT T1.ID,T1.Item,T1.Date,T1.Flag,T1.Quantity
,ISNULL(T3.Balance,0)+SUM(CASE WHEN T1.ID=T2.ID THEN 0 WHEN T2.Flag>0 THEN T2.Quantity ELSE -T2.Quantity END)Opening
,ISNULL(T3.Balance,0)+SUM(CASE WHEN T2.Flag>0 THEN T2.Quantity ELSE -T2.Quantity END)Balance
FROM TB T1
JOIN TB T2 ON T1.Item=T2.Item AND(T1.Date>T2.Date OR(T1.Date=T2.Date AND T1.ID>=T2.ID))
LEFT JOIN Stocks T3 ON T1.Item=T3.Item
GROUP BY T1.ID,T1.Item,T1.Date,T1.Flag,T1.Quantity,T3.Balance
)T
--WHERE Date>='2005-01-01'时间条件放在此处
GROUP BY Item,CONVERT(VARCHAR(7),Date,120)
ORDER BY Item,Date--排序你可以根据你的需要进行修改
------解决思路----------------------
-- 试试这个，我剔除了 2005 以上的语法，看 2000 下能不能跑通。
declare @t table
(
item varchar(10) ,
tdate varchar(10) ,
Opening int ,
[IN] int,
IN_Retrun int ,
[OUT] int ,
OUT_Return int ,
Balance int
)
insert into @t(item , tdate,[in],IN_Retrun,[out],OUT_Return)
select
item , convert(varchar(7),[date],120) ,
sum(case when Flag = 1 and Quantity > 0 then Quantity else 0 end) ,
sum(case when Flag = 1 and Quantity < 0 then -Quantity else 0 end) ,
sum(case when Flag = 0 and Quantity > 0 then Quantity else 0 end) ,
sum(case when Flag = 0 and Quantity < 0 then -Quantity else 0 end)
from tb
group by item , convert(varchar(7),[date],120)
-- 计算当月净结余
update @t set Balance = [IN] - IN_Retrun - [OUT] + OUT_Return
-- select * from @t
select item, tdate , opening , [IN] , IN_Retrun , [OUT] , OUT_Return ,
opening + balance as balance
from (
select A.item , A.tdate ,
(select isnull(SUM(b.Balance),0) from @t B where A.item = b.item and a.tdate > b.tdate) as Opening ,
A.[IN] , A.IN_Retrun , A.[OUT] , A.OUT_Return , A.Balance
from @t A
) x
item tdate opening IN IN_Retrun OUT OUT_Return balance
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
aa 2005-01 0 100 0 0 0 100
cc 2005-01 0 100 0 0 0 100
aa 2005-02 100 380 10 55 5 420
bb 2005-02 0 195 15 65 20 135
aa 2005-03 420 0 10 0 0 410
(5 行受影响)
------解决思路----------------------
设计有误。
Stocks表的字段就应该按照你需要的结果这样设计。
然后按月统计下来，把结果永久保存在Stocks表中。
查询就从Stock表中直接取，不能每次查询都重新计算。