如何高效从库存表获取最后一次入库与出库的时间戳

现有仓库库存交易表dbo.TestForStock，记录每笔入库（Qty>0）、出库（Qty<0）交易。需要获取各仓库中每个物品的最后一次入库时间戳和最后一次出库时间戳，要求用单条高效SQL语句替代原有的两次TOP 1低效查询。

原表结构及测试数据 #

CREATE DATABASE TestDB;
GO
USE TestDB;
CREATE TABLE dbo.TestForStock
( ID INT IDENTITy(1,1)
,WarehouseID BIGINT
,ItemID BIGINT
,Qty INT
,DatetimeStamp datetime
,Remarks NVARCHAR(100)
)

INSERT INTO dbo.TestForStock
SELECT 20,392,100, '2023-06-28 16:57:43.143','Item Added' UNION ALL
SELECT 20,392,-1, '2023-06-29 15:54:41.997','Item removed' UNION ALL
SELECT 20,392,-1, '2023-06-29 15:54:42.037','Item removed' UNION ALL
SELECT 20,128,50, '2023-06-20 17:38:43.030','Item Added' UNION ALL
SELECT 20,128,-1, '2023-06-21 17:38:43.030','Item removed' UNION ALL
SELECT 20,128,-1, '2023-06-22 17:38:43.030','Item removed' UNION ALL
SELECT 20,128,10, '2023-06-25 17:38:43.030','Item Added' UNION ALL
SELECT 20,128,-1, '2023-06-26 17:38:43.030','Item removed' UNION ALL
SELECT 20,128,-1, '2023-06-27 17:38:43.030','Item removed' 

高效SQL解决方案 #

SELECT 
    WarehouseID,
    ItemID,
    MAX(CASE WHEN Qty > 0 THEN Qty END) AS LastInQty,
    MAX(CASE WHEN Qty > 0 THEN DatetimeStamp END) AS LatestPlusDate,
    MAX(CASE WHEN Qty < 0 THEN DatetimeStamp END) AS LatestMinusDate
FROM dbo.TestForStock
GROUP BY WarehouseID, ItemID

该语句通过条件聚合实现单表扫描即可完成统计，相比两次TOP 1查询（需扫描表两次）效率更高。通过CASE语句分别筛选入库、出库记录，再用MAX函数获取每组（仓库+物品）下的最新时间戳，同时返回最后一次入库的数量。

预期结果 #

内容的提问来源于stack exchange，提问作者Hemant Singh Sisodia