按分组列值去重的MySQL查询：库存分配优先满足早请求用户

需求 #

现有两张表：inventory（库存表）和requests（商品请求表），需要编写MySQL查询语句，生成一份库存商品分配清单，明确分配给各用户的商品SKU。核心规则是优先满足requests.id最小的用户，当库存无法覆盖所有同类型商品请求时，仅分配给优先级靠前的请求。

示例表结构与数据 #

建表与插入数据 #

create table inventory (id int, product_type varchar(56), sku varchar(56));
insert into inventory values
(1,     'bike', 'b1'),
(2,     'bike', 'b2'),
(3,     'scooter', 's1'),
(4,     'scooter', 's2'),
(5,     'unicycle', 'u1');

create table requests (id int, user_id int, product_type varchar(56));
insert into requests values
(1,   1,   'bike'),
(2,   2,  'bike'),
(3,   2,  'scooter'),
(4,   3,   'bike'),
(5,   3,   'scooter');

期望输出 #

注：user_id为3的用户申请的bike因库存仅2辆，无法分配。

解决方案SQL #

思路：通过窗口函数分别对请求和库存按商品类型分组、按优先级排序编号，再匹配同类型且编号相同的记录，实现按请求优先级分配库存。

SELECT 
    r.user_id,
    r.product_type,
    i.sku
FROM (
    -- 给每个请求按商品类型、请求ID升序编号
    SELECT 
        user_id,
        product_type,
        ROW_NUMBER() OVER (PARTITION BY product_type ORDER BY id) AS req_rank
    FROM requests
) r
JOIN (
    -- 给每个库存按商品类型、库存ID升序编号
    SELECT 
        product_type,
        sku,
        ROW_NUMBER() OVER (PARTITION BY product_type ORDER BY id) AS inv_rank
    FROM inventory
) i ON r.product_type = i.product_type AND r.req_rank = i.inv_rank
ORDER BY r.product_type, r.req_rank;

执行该查询后，将得到与期望一致的分配结果。

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