【SQL Server | T-SQL】SQL Server 2008 群组累计加总

【SQL Server | T-SQL】SQL Server 2008 群组累计
有时候我们会在数据运算中加上了一个累计加总的数据栏,在SQL Server 2012中可以OVER子句来完成(SQL Server2012 OVER),而今天刚好就遇到需要计算库存的累计数量,在网络上找了许久,2012可以用比较轻松的方式解决,但在2008就没办法使用,所以只好利用下面的方法来做到这项功能了
今天的需求是要统计同一个产品,从期初的库存量计算时间范围内的进货数量及出货数量的统计量,以下是小弟使用CTE的计算方式


有时候我们会在数据运算中加上了一个累计加总的数据栏,在SQL Server 2012中可以OVER子句来完成(SQL Server2012 OVER),而今天刚好就遇到需要计算库存的累计数量,在网络上找了许久,2012可以用比较轻松的方式解决,但在2008就没办法使用,所以只好利用下面的方法来做到这项功能了

今天的需求是要统计同一个产品,从期初的库存量计算时间范围内的进货数量及出货数量的统计量,以下是小弟使用CTE的计算方式

DECLARE @Strat_date date  = '2013/1/1',@End_date date ='2013/12/31';
	--事先定义只要2013年的数据,在2013年以前计算后当作期初数量
	WITH TEMP_START(mid,pqrt_START,ptotal_START) AS 
	(
		SELECT mid,SUM(pqrt) AS pqrt_START,SUM(ptotal) AS ptotal_START  FROM(
			SELECT mid,SUM(pqrt) as pqrt,SUM(ptotal) AS ptotal FROM 进货
			WHERE date <@Strat_date
			GROUP BY mid
			UNION ALL
			SELECT mid,SUM(pqrt) as pqrt,SUM(ptotal) AS ptotal FROM 进货退回
			WHERE date <@Strat_date
			GROUP BY mid
			UNION ALL
			SELECT mid,SUM(pqrt)*-1 as pqrt,SUM(ptotal) AS ptotal FROM 销货
			WHERE date <@Strat_date
			GROUP BY mid
			UNION ALL
			SELECT mid,SUM(pqrt)*-1 as pqrt,SUM(ptotal) AS ptotal FROM 销货退回
			WHERE date <@Strat_date
			GROUP BY mid
		) A
		GROUP BY mid
	),--计算期初数量,将进货、进货退回、销货、销货退回分别计算后,把数据表合并在一个CTE中
	TEMP_UNION AS
	(
		SELECT *,pqrt AS pqrt_IN,ptotal AS ptotal_IN,0 as pqrt_OUT,0 as ptotal_OUT FROM 进货
		WHERE date >= @Strat_date AND date <[email protected]_date
		UNION ALL
		SELECT *,pqrt AS pqrt_IN,ptotal AS ptotal_IN,0 as pqrt_OUT,0 as ptotal_OUT FROM 进货退回
		WHERE date >= @Strat_date AND date <[email protected]_date
		UNION ALL
		SELECT *,0 AS pqrt_IN,0 AS ptotal_IN,pqrt as pqrt_OUT,ptotal as ptotal_OUT FROM 销货
		WHERE date >= @Strat_date AND date <[email protected]_date
		UNION ALL
		SELECT *,0 AS pqrt_IN,0 AS ptotal_IN,pqrt as pqrt_OUT,ptotal as ptotal_OUT FROM 销货退回
		WHERE date >= @Strat_date AND date <[email protected]_date
	),--将2013年的进货数量、进货退回、销货量、销货退回合并在同一张数据表  (事先已将同一笔进货单、进货退回单、销货单、销货退回单依照产品代号加总)
	TEMP_ACC AS 
	(
		SELECT a.mid,a.date,a.code,SUM(b.pqrt_IN)
			-SUM(b.pqrt_OUT) AS total_ACC FROM TEMP_UNION a
		JOIN TEMP_UNION b
		ON a.mid = b.mid AND a.date >= b.date AND (a.date != b.date OR a.code <= b.code)
		GROUP BY a.mid,a.code,a.date
	)--计算累计数量
	/*
		选取产品代号、日期、纪录单号以便作为群组使用(防止同一天有多笔不同纪录单号的进出货)
		先计算进货的总计,与出货的总计,然后将上一个CTE产生的进出货数量相减取总和
		合计的条件是:产品代号相同的,将小于自己日期的加总在一起,而后面的条件是若一天内有多笔纪录,依照纪录单号的大小加总
	*/	
	SELECT TEMP_UNION.mid as [产品代号],TEMP_UNION.[date] as [日期],TEMP_UNION.code as [纪录单号],
		TEMP_UNION.flag as [进出货],a.pqrt_START as [期初数量],TEMP_UNION.pqrt_IN as [进货数量],TEMP_UNION.pqrt_OUT as [出货数量],
	a.pqrt_START + b.total_ACC as [累计数量]
	FROM  TEMP_UNION 
	LEFT OUTER JOIN TEMP_START a
	ON TEMP_UNION.mid = a.mid
	LEFT OUTER JOIN TEMP_ACC b
	ON a.mid = b.mid AND TEMP_UNION.date = b.date AND TEMP_UNION.code = b.code
	LEFT OUTER JOIN SL01
	ON TEMP_UNION.mid = SL01.mid
	WHERE a.mid IS NOT NULL
	ORDER BY TEMP_UNION.mid,TEMP_UNION.date

这段T-SQL执行后会产生下列结果

image

---参考文章---

http://byronhu.wordpress.com/2011/05/14/%E7%94%A8-t-sql-%E7%B5%B1%E8%A8%88%E8%B3%87%E6%96%99/


大家好我是馒头,希望大家喜欢我的文章

如果有错误的地方请不吝指教 ^_^