如何为客户餐厅表填充不重复餐厅值？SQL数据补全需求

现有两张数据表：

• customer_restaurants：存储客户（c_id）对应的三家餐厅信息，部分字段为NULL
• restaurant_list：全量唯一餐厅列表

需要填充customer_restaurants中的NULL值，确保每个客户的三个餐厅列值互不重复。此前使用COALESCE结合ROW_NUMBER()的UPDATE语句会重复填充相同值，无法满足跨列唯一要求。

示例数据 #

customer_restaurants表 #

+-------+-----------+-----------+-----------+
| c_id  |restaurant1|restaurant2|restaurant3|
+-------+-----------+-----------+-----------+
|   1   |   Mc'Ds   |   BurgerK | Chipotle  |
|   2   |   Carls   |   DairyQ  | McD's     |
|   3   |   Jack    |   NULL    | Chipotle  |
|   4   |   TacoBell|   NULL    |  NULL     |
|   5   |   NULL    |   NULL    |  NULL     |
+-------+-----------+-----------+-----------+

restaurant_list表 #

+-------+-----------+
| r_id  |restaurants|
+-------+-----------+
|   A   |   Mc'Ds   |  
|   B   |   Carls   |  
|   C   |   Jack    | 
|   D   |   TacoBell| 
|   E   |   DairyQ  | 
|   F   |   Chipotle| 
|   G   |   DelTaco | 
|   H   |   BurgerK | 
+-------+-----------+

尝试过的SQL语句 #

UPDATE customer_restaurants
SET restaurant1 = COALESCE(restaurant1, restaurant_list.restaurants),
    restaurant2 = COALESCE(restaurant2, restaurant_list.restaurants),
    restaurant3 = COALESCE(restaurant3, restaurant_list.restaurants)
FROM (
    SELECT restaurants, 
           ROW_NUMBER() OVER (ORDER BY restaurants) AS row_num
    FROM restaurant_list
) AS restaurant_list
WHERE
    (restaurant1 IS NULL AND row_num = 1) OR
    (restaurant2 IS NULL AND row_num = 2) OR
    (restaurant3 IS NULL AND row_num = 3) ;

期望最终表结构 #

+-------+-----------+-----------+-----------+
| c_id  |restaurant1|restaurant2|restaurant3|
+-------+-----------+-----------+-----------+
|   1   |   Mc'Ds   |   BurgerK | Chipotle  |
|   2   |   Carls   |   DairyQ  | McD's     |
|   3   |   Jack    |   Mc'Ds   | Chipotle  |
|   4   |   TacoBell|   Mc'Ds   |  Carls    |
|   5   |   Mc'Ds   |   Carls   |  Jack     |
+-------+-----------+-----------+-----------+

解决方案SQL #

以下是基于PostgreSQL语法的实现方案，核心逻辑是先筛选出每个客户未使用过的餐厅，再按顺序分配填充到NULL列，同时保证列间值唯一：

WITH customer_existing AS (
    -- 收集每个客户已有的非NULL餐厅，存入数组
    SELECT 
        c_id,
        ARRAY_REMOVE(ARRAY[restaurant1, restaurant2, restaurant3], NULL) AS existing_restos
    FROM customer_restaurants
),
available_restos_per_customer AS (
    -- 为每个客户匹配所有未使用过的餐厅，并按名称排序分配填充顺序
    SELECT 
        ce.c_id,
        r.restaurants,
        ROW_NUMBER() OVER (PARTITION BY ce.c_id ORDER BY r.restaurants) AS fill_rank
    FROM customer_existing ce
    CROSS JOIN restaurant_list r
    WHERE r.restaurants <> ALL(ce.existing_restos)
),
fill_candidates AS (
    -- 将候选填充餐厅转成列格式，方便后续更新
    SELECT 
        c_id,
        MAX(CASE WHEN fill_rank = 1 THEN restaurants END) AS fill_1,
        MAX(CASE WHEN fill_rank = 2 THEN restaurants END) AS fill_2,
        MAX(CASE WHEN fill_rank = 3 THEN restaurants END) AS fill_3
    FROM available_restos_per_customer
    GROUP BY c_id
)
UPDATE customer_restaurants cr
SET 
    -- 填充第一列的NULL
    restaurant1 = COALESCE(cr.restaurant1, fc.fill_1),
    -- 填充第二列的NULL，确保不与第一列重复
    restaurant2 = COALESCE(cr.restaurant2, 
        CASE WHEN cr.restaurant1 <> fc.fill_1 THEN fc.fill_1 ELSE fc.fill_2 END),
    -- 填充第三列的NULL，确保不与前两列重复
    restaurant3 = COALESCE(cr.restaurant3,
        CASE
            WHEN cr.restaurant1 <> fc.fill_1 AND cr.restaurant2 <> fc.fill_1 THEN fc.fill_1
            WHEN cr.restaurant1 <> fc.fill_2 AND cr.restaurant2 <> fc.fill_2 THEN fc.fill_2
            ELSE fc.fill_3
        END)
FROM fill_candidates fc
WHERE cr.c_id = fc.c_id;

逻辑说明 #

1. customer_existing：将每个客户的三个餐厅列合并为数组，并移除NULL值，得到该客户已使用的餐厅列表。
2. available_restos_per_customer：通过交叉连接为每个客户匹配所有未在已使用列表中的餐厅，并用ROW_NUMBER()为这些餐厅排序，确定填充优先级。
3. fill_candidates：将排序后的候选餐厅转成列格式，方便对应到原表的三个餐厅列。
4. UPDATE语句：依次填充每个NULL列，通过CASE判断确保填充的值不与已存在的列值重复，最终满足每个客户的三个餐厅值唯一的要求。

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