SQL按国家匹配插入名次统计值到新表的NULL异常问题

你当前的写法存在两个核心错误：

• 分两次执行独立INSERT操作：第一次仅插入Country字段，未给四个计数字段赋值，默认填充NULL；第二次仅插入计数字段，未给Country字段赋值，默认填充NULL。两次操作都是新增独立行，没有按国家做匹配关联，必然出现国家和计数值拆分到不同行的错位问题。
• 逻辑存在漏洞：一是引用了未创建的temp_wc表，二是仅从Home Team Name字段取国家列表会遗漏参赛国家，三是未对未获奖国家的计数值做0值填充。

先清空之前写入的脏数据，再通过一次关联查询完成全量数据写入，不需要分步插入：

-- 清空错误数据
TRUNCATE TABLE project.worldcup;

-- 一次性写入全量国家及对应获奖次数
INSERT INTO project.worldcup (Country, Winner_count, Runner_up_Count, Third_count, Fourth_Count)
WITH all_participate_countries AS (
    -- 提取所有出现在冠/亚/季/殿名单中的国家，去重得到完整国家列表
    SELECT Winners AS country_name FROM project.worldcupmatches
    UNION
    SELECT `Runners-up` AS country_name FROM project.worldcupmatches
    UNION
    SELECT Third AS country_name FROM project.worldcupmatches
    UNION
    SELECT Fourth AS country_name FROM project.worldcupmatches
    -- 若需要纳入所有主场参赛国家，取消下面一行的注释即可
    -- UNION SELECT `Home Team Name` AS country_name FROM project.worldcupmatches
),
count_winner AS (
    SELECT Winners AS country_name, COUNT(*) AS win_cnt FROM project.worldcupmatches GROUP BY Winners
),
count_runnerup AS (
    SELECT `Runners-up` AS country_name, COUNT(*) AS ru_cnt FROM project.worldcupmatches GROUP BY `Runners-up`
),
count_third AS (
    SELECT Third AS country_name, COUNT(*) AS third_cnt FROM project.worldcupmatches GROUP BY Third
),
count_fourth AS (
    SELECT Fourth AS country_name, COUNT(*) AS fourth_cnt FROM project.worldcupmatches GROUP BY Fourth
)
SELECT
    apc.country_name,
    IFNULL(cw.win_cnt, 0) AS Winner_count,
    IFNULL(cr.ru_cnt, 0) AS Runner_up_Count,
    IFNULL(ct.third_cnt, 0) AS Third_count,
    IFNULL(cf.fourth_cnt, 0) AS Fourth_Count
FROM all_participate_countries apc
LEFT JOIN count_winner cw ON apc.country_name = cw.country_name
LEFT JOIN count_runnerup cr ON apc.country_name = cr.country_name
LEFT JOIN count_third ct ON apc.country_name = ct.country_name
LEFT JOIN count_fourth cf ON apc.country_name = cf.country_name;

• 用UNION合并四个名次列的所有国家并去重，不会遗漏获得过名次的参赛国
• 分别聚合计算每个国家冠、亚、季、殿的获得次数
• 通过左连接匹配全量国家和各名次统计结果，未获得对应名次的国家用IFNULL将空值转为0，不会出现NULL值
• 单条INSERT语句完成整行写入，从根源避免国家和计数值错位到不同行的问题
• 注意不要使用原始表中代表举办国的Country字段提取参赛国家名单，避免统计错误

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