如何将Customer_Foods单行多列值与Food_Options单列值对比并填充空值

我有两张表Customer_Foods和Food_Options，Customer_Foods的部分列存在空值（BLANK）。需要完成两个目标：

• 找出指定ID行中，Customer_Foods里不在Food_Options列表中的值；
• 用Food_Options中的选项填充Customer_Foods的空值，且每个ID的填充选项不能重复。

Customer_Foods表 #

Food_Options表 #

我尝试用NOT IN查询，但没法把Customer_Foods单行的多列值整合为可与Food_Options单列对比的列表。下面的查询在子查询指定单列（比如Food1）时能运行，但没法同时处理所有列：

SELECT *
FROM Food_Options
WHERE Foods NOT IN (SELECT *
                    FROM Customer_Foods
                    WHERE ID = 113)

SELECT *
FROM Food_Options
WHERE Foods NOT IN (SELECT Food1
                    FROM Customer_Foods
                    WHERE ID = 113)

解决方案 #

步骤1：提取指定ID的所有非空食物值 #

用UNION ALL把指定ID行的多列食物值合并成单列，同时过滤空值：

SELECT Food1 AS food FROM Customer_Foods WHERE ID = 113 AND Food1 <> 'BLANK'
UNION ALL
SELECT Food2 AS food FROM Customer_Foods WHERE ID = 113 AND Food2 <> 'BLANK'
UNION ALL
SELECT Food3 AS food FROM Customer_Foods WHERE ID = 113 AND Food3 <> 'BLANK'
UNION ALL
SELECT Food4 AS food FROM Customer_Foods WHERE ID = 113 AND Food4 <> 'BLANK'

步骤2：找出不在Food_Options中的值 #

推荐用NOT EXISTS（避免空值导致的逻辑问题），结合上面的子查询：

SELECT f.Foods
FROM Food_Options f
WHERE NOT EXISTS (
    SELECT 1
    FROM (
        SELECT Food1 AS food FROM Customer_Foods WHERE ID = 113 AND Food1 <> 'BLANK'
        UNION ALL
        SELECT Food2 AS food FROM Customer_Foods WHERE ID = 113 AND Food2 <> 'BLANK'
        UNION ALL
        SELECT Food3 AS food FROM Customer_Foods WHERE ID = 113 AND Food3 <> 'BLANK'
        UNION ALL
        SELECT Food4 AS food FROM Customer_Foods WHERE ID = 113 AND Food4 <> 'BLANK'
    ) AS c
    WHERE c.food = f.Foods
)

步骤3：填充空值（保证不重复） #

以MySQL为例，先把行转成键值对处理，再匹配空值位置和可用食物，最后更新原表：

-- 把指定ID的列转成行格式
WITH cte AS (
    SELECT ID, 'Food1' AS col, Food1 AS food FROM Customer_Foods WHERE ID = 113
    UNION ALL
    SELECT ID, 'Food2' AS col, Food2 AS food FROM Customer_Foods WHERE ID = 113
    UNION ALL
    SELECT ID, 'Food3' AS col, Food3 AS food FROM Customer_Foods WHERE ID = 113
    UNION ALL
    SELECT ID, 'Food4' AS col, Food4 AS food FROM Customer_Foods WHERE ID = 113
),
-- 筛选该ID已使用的食物
used_foods AS (
    SELECT food FROM cte WHERE food <> 'BLANK'
),
-- 获取可用填充食物并排序
available_foods AS (
    SELECT Foods, ROW_NUMBER() OVER (ORDER BY Foods) AS rn
    FROM Food_Options
    WHERE Foods NOT IN (SELECT food FROM used_foods)
),
-- 标记需要填充的空列位置
empty_cols AS (
    SELECT col, ROW_NUMBER() OVER (ORDER BY col) AS rn
    FROM cte
    WHERE food = 'BLANK'
)
-- 更新原表的空值
UPDATE Customer_Foods cf
JOIN (
    SELECT ec.col, af.Foods
    FROM empty_cols ec
    JOIN available_foods af ON ec.rn = af.rn
) AS fill ON 
    (fill.col = 'Food1' AND cf.ID = 113) OR
    (fill.col = 'Food2' AND cf.ID = 113) OR
    (fill.col = 'Food3' AND cf.ID = 113) OR
    (fill.col = 'Food4' AND cf.ID = 113)
SET
    cf.Food1 = CASE WHEN fill.col = 'Food1' THEN fill.Foods ELSE cf.Food1 END,
    cf.Food2 = CASE WHEN fill.col = 'Food2' THEN fill.Foods ELSE cf.Food2 END,
    cf.Food3 = CASE WHEN fill.col = 'Food3' THEN fill.Foods ELSE cf.Food3 END,
    cf.Food4 = CASE WHEN fill.col = 'Food4' THEN fill.Foods ELSE cf.Food4 END
WHERE cf.ID = 113;

其他数据库（如SQL Server）语法略有差异，但核心逻辑一致：先拆分列成行，匹配空值位置和可用食物，再转回列更新。

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