SQL实现按item_id取最新库存盘点记录合并后续入库数据

解决方案见文末。

现有两张业务表：一张存储人工库存盘点记录，另一张存储库存入库计数记录。

表结构与样例数据 #

库存盘点表（inventory_count） #

字段包含timestamp、item_id、units_counted，样例数据如下：

库存入库表（inventory_received） #

字段包含timestamp、item_id、units_received，样例数据如下：

需要创建视图实现以下逻辑：

• 先取出盘点表中每个item_id对应的最新一条记录
• 将上述记录，与入库表中同一item_id下timestamp大于该item_id最新盘点记录timestamp的条目做UNION合并

期望输出字段为timestamp、item_id、units，样例输出如下：

最初编写的查询存在逻辑问题：仅能匹配全表最新的盘点时间，无法按每个item_id分别取对应的最新盘点时间进行关联匹配，初始代码如下：

SELECT
  a.[timestamp], a.sales_item_id, a.sales_item_name, a.unit_cost, a.unit_count, a.unit_cost_total, b.sales_subcat_name, b.sales_category_name, b.sales_department_name
FROM
  [DB].[dbo].[inventory_count] as a
  LEFT JOIN [DB].[dbo].[Inventory_Categorized] as b
  ON a.sales_item_id = b.sales_item_id
WHERE
  [timestamp] = (SELECT MAX([timestamp]) FROM inventory_count i WHERE i.sales_item_id = a.sales_item_id)

UNION ALL

SELECT
  a.[timestamp], a.sales_item_id, a.sales_item_name, a.unit_cost, a.units_received, a.unit_cost_total, b.sales_subcat_name, b.sales_category_name, b.sales_department_name
FROM
  inventory_count as c,
  inventory_received as a
  LEFT JOIN [DB].[dbo].[Inventory_Categorized] as b
  ON a.sales_item_id = b.sales_item_id
WHERE
  a.[timestamp] > (SELECT MAX([timestamp]) FROM inventory_count i WHERE i.sales_item_id = a.sales_item_id)

参考社区指引，最终采用CTE+ROW_NUMBER()窗口函数实现了可用方案，该方案同时兼容查询无对应盘点记录的入库数据，代码如下：

WITH cte_count AS (
    SELECT [timestamp],
           sales_item_id,
           unit_count AS units,
           sales_item_name,
           ROW_NUMBER() OVER (PARTITION BY sales_item_id
                              ORDER BY [timestamp] DESC)
                        AS row_number
    FROM inventory_count
) SELECT [timestamp], sales_item_id, units, sales_item_name
FROM cte_count
WHERE row_number = 1

UNION

SELECT r.[timestamp], r.sales_item_id, r.units_received AS units, r.sales_item_name
FROM inventory_received AS r, cte_count AS c
WHERE r.[timestamp] > c.[timestamp] and c.row_number = 1

UNION

SELECT r.[timestamp], r.sales_item_id, r.units_received AS units, r.sales_item_name
FROM inventory_received r
LEFT JOIN cte_count c ON c.sales_item_id = r.sales_item_id
WHERE c.sales_item_id IS NULL

该方案虽存在优化空间，但可完全满足业务需求，通过本次问题解决也掌握了对应场景的SQL写法，后续可复用该逻辑处理同类开发任务。

内容的提问来源于stack exchange，提问作者ChristmasInOct