如何编写SQL查询为用户分配唯一的喜爱书籍

表user_book记录了用户喜爱的书籍，表结构与测试数据如下：

CREATE TABLE user_book (
  user_id INT,
  book_id INT,
  FOREIGN KEY (user_id) REFERENCES user(id),
  FOREIGN KEY (book_id) REFERENCES book(id)
);

insert into user_book (user_id, book_id) values
(1, 1),
(1, 2),
(1, 5),
(2, 2),
(2, 5),
(3, 2),
(3, 5);

需要编写SQL查询（可使用多语句WITH子句，禁止使用存储过程），为至少有一本喜爱书籍的用户分配一本喜爱书籍，需满足：

• 同一本书不能分配给不同用户
• 分配逻辑可采用朴素规则：逐个处理用户，随机为当前用户分配仍可用的喜爱书籍，无需考虑后续用户的分配情况（允许部分用户分不到书、部分书籍未被分配）

方案1：递归CTE模拟逐个用户分配 #

通过递归CTE模拟“逐个处理用户、随机选可用书籍”的逻辑，步骤如下：

1. 给所有有喜爱书籍的用户随机排序，确定处理顺序
2. 给每个用户的喜爱书籍随机排序，确定候选优先级
3. 递归遍历用户，每次为当前用户分配其候选列表中第一本未被占用的书籍

WITH RECURSIVE
-- 随机确定用户处理顺序
user_order AS (
    SELECT DISTINCT user_id,
           ROW_NUMBER() OVER (ORDER BY RAND()) AS user_rank
    FROM user_book
),
-- 给每个用户的喜爱书籍随机排序，确定候选优先级
user_book_ranked AS (
    SELECT ub.user_id, ub.book_id,
           ROW_NUMBER() OVER (PARTITION BY ub.user_id ORDER BY RAND()) AS book_rank
    FROM user_book ub
),
-- 递归执行分配
allocation AS (
    -- 初始：处理第一个用户，取其第一本候选书籍
    SELECT uo.user_id, ubr.book_id,
           uo.user_rank
    FROM user_order uo
    JOIN user_book_ranked ubr ON uo.user_id = ubr.user_id
    WHERE uo.user_rank = 1 AND ubr.book_rank = 1
    UNION ALL
    -- 递归：处理下一个用户，取其未被分配的第一本候选书籍
    SELECT uo.user_id, ubr.book_id,
           uo.user_rank
    FROM allocation a
    JOIN user_order uo ON uo.user_rank = a.user_rank + 1
    JOIN user_book_ranked ubr ON uo.user_id = ubr.user_id
    WHERE ubr.book_id NOT IN (SELECT book_id FROM allocation)
    ORDER BY ubr.book_rank
    LIMIT 1
)
SELECT user_id, book_id
FROM allocation;

方案2：基于优先级的非递归分配 #

通过窗口函数为用户和书籍标记优先级，确保每本书仅分配给第一个申请它的用户，逻辑更简洁：

1. 给用户随机排序，同时给每个用户的喜爱书籍随机排序
2. 对每本书，标记出优先级最高的申请者，仅保留该分配记录

WITH user_book_random AS (
    -- 给用户随机排序，同时给每个用户的喜爱书籍随机排序
    SELECT user_id, book_id,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY RAND()) AS user_book_seq,
           ROW_NUMBER() OVER (ORDER BY RAND()) AS user_seq
    FROM user_book
),
book_claim AS (
    -- 给每本书标记优先级最高的申请者
    SELECT user_id, book_id,
           ROW_NUMBER() OVER (PARTITION BY book_id ORDER BY user_seq, user_book_seq) AS claim_seq
    FROM user_book_random
)
SELECT user_id, book_id
FROM book_claim
WHERE claim_seq = 1;

说明 #

两种方案均满足需求：

• 保证同一本书不会被分配给多个用户
• 分配结果随机，允许部分用户分不到书、部分书籍未被分配
• 仅使用WITH子句，未使用存储过程

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