Snowflake中处理订单表：用客户未点过的随机餐食填充空值

原SQL的核心问题是：子查询从整个ordertable中筛选所有空值行的餐食，相当于排除了所有客户的已点餐，而非当前要更新的单个客户的已点餐，导致逻辑不符合需求。

以下是针对不同数据库环境的解决方案：

PostgreSQL 版本 #

由于你使用了RANDOM()，默认对应PostgreSQL环境，以下语句会针对单个客户排除已点餐食：

分步更新各空字段 #

分别处理option1、option2、option3的空值，每次更新时都会排除当前客户已有的所有餐食（包括已填充的字段）：

-- 更新option1的空值
UPDATE ordertable t
SET option1 = (
    SELECT m.meal
    FROM meals m
    WHERE m.meal NOT IN (
        SELECT unnest(array[t.option1, t.option2, t.option3])
        WHERE unnest(array[t.option1, t.option2, t.option3]) IS NOT NULL
    )
    ORDER BY RANDOM()
    LIMIT 1
)
WHERE t.option1 IS NULL;

-- 更新option2的空值
UPDATE ordertable t
SET option2 = (
    SELECT m.meal
    FROM meals m
    WHERE m.meal NOT IN (
        SELECT unnest(array[t.option1, t.option2, t.option3])
        WHERE unnest(array[t.option1, t.option2, t.option3]) IS NOT NULL
    )
    ORDER BY RANDOM()
    LIMIT 1
)
WHERE t.option2 IS NULL;

-- 更新option3的空值
UPDATE ordertable t
SET option3 = (
    SELECT m.meal
    FROM meals m
    WHERE m.meal NOT IN (
        SELECT unnest(array[t.option1, t.option2, t.option3])
        WHERE unnest(array[t.option1, t.option2, t.option3]) IS NOT NULL
    )
    ORDER BY RANDOM()
    LIMIT 1
)
WHERE t.option3 IS NULL;

逻辑说明

• unnest(array[t.option1, t.option2, t.option3])：将当前客户的三个点餐选项拆分为单独行，过滤null值后得到该客户所有已点过的餐食。
• 子查询从meals表中排除这些已点餐食，随机选取一个填充对应的空字段。

MySQL 版本 #

如果使用MySQL（无unnest函数），可以用以下写法：

-- 更新option1的空值
UPDATE ordertable t
SET option1 = (
    SELECT m.meal
    FROM meals m
    WHERE m.meal != COALESCE(t.option1, '')
      AND m.meal != COALESCE(t.option2, '')
      AND m.meal != COALESCE(t.option3, '')
    ORDER BY RAND()
    LIMIT 1
)
WHERE t.option1 IS NULL;

-- 更新option2的空值
UPDATE ordertable t
SET option2 = (
    SELECT m.meal
    FROM meals m
    WHERE m.meal != COALESCE(t.option1, '')
      AND m.meal != COALESCE(t.option2, '')
      AND m.meal != COALESCE(t.option3, '')
    ORDER BY RAND()
    LIMIT 1
)
WHERE t.option2 IS NULL;

-- 更新option3的空值
UPDATE ordertable t
SET option3 = (
    SELECT m.meal
    FROM meals m
    WHERE m.meal != COALESCE(t.option1, '')
      AND m.meal != COALESCE(t.option2, '')
      AND m.meal != COALESCE(t.option3, '')
    ORDER BY RAND()
    LIMIT 1
)
WHERE t.option3 IS NULL;

逻辑说明

• COALESCE(t.optionX, '')：将null值转为空字符串，避免与!=比较时出现逻辑错误。
• 筛选条件排除当前客户已有的所有非空餐食，随机选取一个填充空字段。

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