引用他人数据：
--结存表
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
---------- ---------------- -------------- ----------- ---------------- ----------- ------------------- -----------
aa 2005-02 225 200 10 0 5 420
aa 2005-03 420 0 0 0 10 430
bb 2005-02 35 195 15 65 20 135
cc 2005-02 100 0 0 0 0 100
--*/
要求结果：
------解决思路----------------------
;WITH CTE AS(
SELECT Item,CONVERT(VARCHAR(7),Date,120)Date
,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]
FROM TB
GROUP BY Item,CONVERT(VARCHAR(7),Date,120)
)
,CTE2 AS(
SELECT T1.Item,T1.[Date],T1.[IN],T1.[IN_Retrun],T1.[OUT],T1.[OUT_Return]
,SUM(CASE WHEN T1.Date=T2.Date THEN 0
ELSE T2.[IN]-T2.[IN_Retrun]-T2.[OUT]+T2.[OUT_Return]
END)Opening
,SUM(T2.[IN]-T2.[IN_Retrun]-T2.[OUT]+T2.[OUT_Return])Balance
FROM CTE T1
JOIN CTE T2 ON T1.Item=T2.Item AND T1.Date>=T2.Date
GROUP BY T1.Item,T1.[Date],T1.[IN],T1.[IN_Retrun],T1.[OUT],T1.[OUT_Return]
)
SELECT T1.Item,T1.Date,T1.Opening+ISNULL(T2.Balance,0) Opening
,T1.[IN],T1.[IN_Retrun],T1.[OUT],T1.[OUT_Return]
,T1.Balance+ISNULL(T2.Balance,0) Balance
FROM CTE2 T1
LEFT JOIN Stocks T2 ON T1.Item=T2.Item
WHERE T1.Date>='2005-01'--按月时间条件放在这
ORDER BY Item,Date
------解决思路----------------------
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