记一次商品库存数及库龄的SQLSERVER数据库统计查询的简单例子
Sql Server 有关库龄的问题代码如何编写
这个是在csdn上看到一位题主的提出的悬赏问题 按着题主问题做了一遍 感觉差不多 这里记录一下自己解决思路(不一定解决了题主问题)
先描述下商品库存表结构
字段名 | 字段类型 | 字段说明 |
---|---|---|
出入库表 | varchar(50) | 入库或出库 |
商品编号 | int | |
出入库日期 | date | |
数量 | int | 新增或减少的数量 |
最终sql语句查询结果的效果需要 得出商品编号 ,库龄以及剩余库存
首先参照表结构创建一个虚拟表 #商品
CREATE TABLE #商品(
出入库表 VARCHAR(50) NOT NULL,
商品编号 INT NOT NULL,
出入库日期 DATETIME NOT NULL,
数量 INT NOT NULL
)
填充模拟出入库数据
实现的sql查询语句脚本
附加 库龄规则 :
- 出库后没有新入库数据情况下 库龄=最新一次的出库时间 - 最早的入库时间
- 出库后有新入库数据的情况下 库龄=最新一次的入库时间 - 最早的入库时间
SELECT 入库.商品编号 ,
( CASE WHEN 入库.最新入库时间 > 出库.日期 THEN DATEDIFF(DAY, 出库.日期, 入库.最新入库时间)
ELSE DATEDIFF(DAY, 入库.日期, 出库.日期)
END ) AS 库龄 ,-- 出库后没有新入库数据情况下 库龄=最新一次的出库时间 - 最早的入库时间
--出库后有新入库数据的情况下 库龄=最新一次的入库时间 - 最早的入库时间
入库.库存数 - 出库.出库数 AS 库存
FROM ( SELECT 商品编号 ,
SUM(数量) 库存数 ,
MIN(出入库日期) 日期 ,
MAX(出入库日期) 最新入库时间
FROM #商品
WHERE 出入库表 = '入库'
AND 出入库日期 < '2020-2-20'
GROUP BY 商品编号
) AS 入库
LEFT JOIN ( SELECT 商品编号 ,
SUM(数量) 出库数 ,
MAX(出入库日期) 日期
FROM #商品
WHERE 出入库表 = '出库'
AND 出入库日期 < '2020-2-20'
GROUP BY 商品编号
) AS 出库 ON 出库.商品编号 = 入库.商品编号
语句查询逻辑
- 先提取入库类型的 数据用商品编号进行分组,得出入库数量总和 以及最早的一个入库日期 得到表【入库】
- 再提取出库类型的数据用商品编号进行分组,得到出库数量总和 以及最后一次出库日期 得到表【出库】
- 对入库和出库表进行left join联合查询 用商品编号左关联
- 根据结果得出最后出库日期以及最早入库日期 计算得出时间差 库龄 入库库存数减去出库数 得到库存