如何实现按intnumber降序、timestamp升序的唯一匹配表连接

这个需求的核心是每条仓库库存仅能被一次申请占用，按优先级给申请分配对应库存，普通关联查询会出现重复匹配、库存被多次占用的问题，通用实现方案如下（主流支持窗口函数的数据库均可直接运行，包括MySQL 8.0+/PostgreSQL/Oracle/SQL Server）：

实现逻辑 #

• 给表A（申请单）的每条记录按物料分组生成唯一序号rn_a，同物料下的序号只要能区分不同申请即可，排序规则可根据申请单的主键、创建时间等稳定字段调整
• 给表B（仓库库存）的每条记录按物料分组，严格按intnumber降序、timestamp升序的规则生成唯一序号rn_b
• 关联时匹配「物料ID相等 + 序号相等」，即可保证每条B库存仅被使用一次，每条A申请匹配到当前优先级最高的未占用库存

参考SQL代码 #

WITH 
-- 对申请单按物料分组生成序号
a_ranked AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY materialid ORDER BY 申请单主键/申请时间) AS rn_a
  FROM 表A
),
-- 对库存按要求的优先级排序生成序号
b_ranked AS (
  SELECT 
    materialid,
    intnumber,
    timestamp,
    ROW_NUMBER() OVER (PARTITION BY materialid ORDER BY intnumber DESC, timestamp ASC) AS rn_b
  FROM 表B
)
-- 按物料和序号关联，实现一一匹配不重复
SELECT a.materialid, b.intnumber, b.timestamp
FROM a_ranked a
INNER JOIN b_ranked b
  ON a.materialid = b.materialid 
  AND a.rn_a = b.rn_b;

示例验证结果 #

对应给出的测试数据，执行后生成的序号集如下：

• 申请单序号（a_ranked）：

• 库存序号（b_ranked）：

最终关联输出和预期结果完全一致：

补充说明 #

• 若使用不支持窗口函数的旧版本数据库（如MySQL 5.x），可通过自定义用户变量模拟行号生成逻辑，匹配规则完全不变
• 若同物料申请数量大于库存数量，使用INNER JOIN会自动丢弃无匹配库存的申请；若需保留所有申请记录（无匹配项显示为NULL），将关联方式改为LEFT JOIN即可
• 若业务上intnumber的优先级逻辑和预期相反，直接调整对应排序的升降序即可

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