如何在MySQL中通过外键将多行值合并到另一表的列中

现有两张表news和files：

• files表通过news_id外键关联news表，同一news_id可对应多条files记录（最多3条），部分news_id无关联记录
• 需要将files表中cat_id=13的name字段值，按顺序填充到news表的file_1、file_2、file_3列，无对应文件的列填0

表结构及初始数据 #

files表 #

news表（初始状态） #

尝试的SQL及错误 #

用户尝试了以下SQL语句：

INSERT INTO news(files_1,files_2,files_3)
SELECT t2.name
FROM files t2
left JOIN news t1 ON t2.file_id = t1.news_id
WHERE t2.cat_id LIKE 13

INSERT INTO news(files_1, files_2, files_3)
SELECT COALESCE( t2.name, '')
FROM files t2
LEFT JOIN news t1 ON t2.file_id = t1.news_id
WHERE t2.cat_id LIKE 13

报错信息：column count doesn't match value count at row 1

问题分析 #

1. 操作类型错误：news表已有数据，需要更新而非插入（INSERT）新记录
2. 列数不匹配：INSERT指定3列，但SELECT仅返回1列，导致列数不匹配
3. 关联条件错误：t2.file_id = t1.news_id是错误的关联逻辑，应该用t2.news_id = t1.news_id关联两张表

解决方案 #

通过行号标记+条件聚合获取每个news_id对应的3个文件，再关联news表进行更新：

完整SQL语句 #

-- 先对files表中cat_id=13的记录按news_id分组并标记行号
WITH ranked_files AS (
    SELECT 
        news_id,
        name,
        ROW_NUMBER() OVER (PARTITION BY news_id ORDER BY file_id) AS rn
    FROM files
    WHERE cat_id = 13
),
-- 聚合每个news_id的3个文件
file_agg AS (
    SELECT 
        news_id,
        COALESCE(MAX(CASE WHEN rn = 1 THEN name END), '0') AS file_1,
        COALESCE(MAX(CASE WHEN rn = 2 THEN name END), '0') AS file_2,
        COALESCE(MAX(CASE WHEN rn = 3 THEN name END), '0') AS file_3
    FROM ranked_files
    GROUP BY news_id
)
-- 更新news表
UPDATE news
SET 
    file_1 = COALESCE(fa.file_1, '0'),
    file_2 = COALESCE(fa.file_2, '0'),
    file_3 = COALESCE(fa.file_3, '0')
FROM news n
LEFT JOIN file_agg fa ON n.news_id = fa.news_id;

执行后news表效果 #

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