您现在的位置是：首页分享心得 > Sql Server 使用 LAG 计算库龄的SQL 语句网站首页分享心得

表结构如下：

(出入库表) (商品编号) (出入库日期) (数量)
入库 123 2020-01-01 100
入库 234 2020-01-02 100
出库 123 2020-01-03 50
出库 234 2020-01-04 50
出库 123 2020-01-05 50
出库 234 2020-01-06 50
入库 123 2020-01-07 100
入库 234 2020-01-08 100
出库 123 2020-01-09 30
出库 234 2020-01-10 30
入库 123 2020-01-11 50
入库 234 2020-01-11 50

按截止指定时间搜索结果 2020-1-20
按先进先出原则

想要达成这样的效果 计算出库龄
(商品编号) (库龄) (库存)

逻辑分析：
123 第一次入库时间是 2020-01-01 入库量是100 ，
出库时间和出库数量分别是 123 2020-01-03 50 和 123 2020-01-05 50
123这里为止第一次入库的库龄是0
123 第二次入库 2020-01-07 100
出库 123 2020-01-09 30
那么123的库存是 70 ，查 2020-1-20 的库龄就应该是 14

那么实现的SQL语句如下：

SELECT
[商品编号],
sum([库存]) [库存],
DATEDIFF( DAY, min([出入库日期]), '2020-1-20' )+1 库龄
FROM
(SELECT
		[商品编号],
		[出入库日期],
		[库存] - ISNULL( LAG ( [库存] ) OVER ( partition BY [商品编号] ORDER BY [出入库日期] ), 0 ) 库存 
	FROM
		(
		SELECT
			[商品编号],
			[数量],
			[出入库日期],
			SUM ( CASE WHEN [出入库表]='入库' THEN [数量] ELSE 0 END ) OVER ( PARTITION BY [商品编号] ORDER BY [出入库日期] )-SUM ( CASE WHEN [出入库表]='出库' THEN [数量] ELSE 0 END ) OVER ( PARTITION BY [商品编号] ) 库存 
		FROM
			[a] 
		WHERE
			[出入库日期] <= '2020-1-20'
		) b
	WHERE
		b.[库存] > 0 
	AND b.[数量] >0) c where [出入库日期] <= '2020-1-20' GROUP BY [商品编号]

实现的效果如下：

下面我们分析一下实现的过程

第一步，我们先要算出2020-1-20 之前的所有日期出入库的库存

SELECT
			[商品编号],
			[数量],
			[出入库日期],
			SUM ( CASE WHEN [出入库表]='入库' THEN [数量] ELSE 0 END ) OVER ( PARTITION BY [商品编号] ORDER BY [出入库日期] )-SUM ( CASE WHEN [出入库表]='出库' THEN [数量] ELSE 0 END ) OVER ( PARTITION BY [商品编号] ) 库存 
		FROM
			[a] 
		WHERE
			[出入库日期] <= '2020-1-20'

结果如下：

第二步，筛选出库存大于0的，

SELECT
		[商品编号],
		[出入库日期],
		[库存] - ISNULL( LAG ( [库存] ) OVER ( partition BY [商品编号] ORDER BY [出入库日期] ), 0 ) 库存 
	FROM
		(
		SELECT
			[商品编号],
			[数量],
			[出入库日期],
			SUM ( CASE WHEN [出入库表]='入库' THEN [数量] ELSE 0 END ) OVER ( PARTITION BY [商品编号] ORDER BY [出入库日期] )-SUM ( CASE WHEN [出入库表]='出库' THEN [数量] ELSE 0 END ) OVER ( PARTITION BY [商品编号] ) 库存 
		FROM
			[a] 
		WHERE
			[出入库日期] <= '2020-1-20'
		) b
	WHERE
		b.[库存] > 0 
	AND b.[数量] >0

结果如下：

第三步，计算出所有产品的库龄：

SELECT
[商品编号],
sum([库存]) [库存],
DATEDIFF( DAY, min([出入库日期]), '2020-1-20' )+1 库龄
FROM
(SELECT
		[商品编号],
		[出入库日期],
		[库存] - ISNULL( LAG ( [库存] ) OVER ( partition BY [商品编号] ORDER BY [出入库日期] ), 0 ) 库存 
	FROM
		(
		SELECT
			[商品编号],
			[数量],
			[出入库日期],
			SUM ( CASE WHEN [出入库表]='入库' THEN [数量] ELSE 0 END ) OVER ( PARTITION BY [商品编号] ORDER BY [出入库日期] )-SUM ( CASE WHEN [出入库表]='出库' THEN [数量] ELSE 0 END ) OVER ( PARTITION BY [商品编号] ) 库存 
		FROM
			[a] 
		WHERE
			[出入库日期] <= '2020-1-20'
		) b
	WHERE
		b.[库存] > 0 
	AND b.[数量] >0) c where [出入库日期] <= '2020-1-20' GROUP BY [商品编号]

结果如下：