如何在SQLite3中合并两表统计各年代男女数量及比例

你的原SQL存在几个关键问题导致无法合并汇总：

• 表名table 1包含空格，应为table1
• UNION ALL的第二个分支缺少SELECT关键字，不符合语法规范
• WHERE子句同时引用两个表的字段，这在UNION ALL场景下无效（UNION是合并两个独立查询的结果，不是关联表）
• 原逻辑是分别对两个表分组后再合并，得到的是两个表各自的统计，而非合并后的汇总结果

方案一：先合并原始数据再分组汇总（推荐，逻辑清晰） #

SELECT
    year_decade AS Year,
    SUM(male_count) AS "Sum of male",
    SUM(female_count) AS "Sum of female",
    -- 转换为浮点型计算比例，避免SQLite整数除法截断
    CAST(SUM(male_count) AS REAL) / CAST(SUM(female_count) AS REAL) AS "Male/Female Ratio"
FROM (
    -- 提取table1的有效数据，转换为年代分组和单条记录的性别计数
    SELECT
        YEAR / 10 * 10 AS year_decade,
        CASE WHEN SEX LIKE 'Male%' THEN 1 ELSE 0 END AS male_count,
        CASE WHEN SEX LIKE 'Female%' THEN 1 ELSE 0 END AS female_count
    FROM table1
    WHERE YEAR IS NOT NULL  -- 若YEAR是字符串类型，改为 YEAR != ''
    UNION ALL
    -- 提取table2的同结构数据
    SELECT
        YEAR / 10 * 10 AS year_decade,
        CASE WHEN SEX LIKE 'Male%' THEN 1 ELSE 0 END AS male_count,
        CASE WHEN SEX LIKE 'Female%' THEN 1 ELSE 0 END AS female_count
    FROM table2
    WHERE YEAR IS NOT NULL
) AS combined_data
GROUP BY year_decade
ORDER BY year_decade;

方案说明： #

1. 子查询通过UNION ALL合并两个表的所有有效数据（排除YEAR为空的记录），将每条记录转换为「年代分组+男性计数+女性计数」的统一格式
2. 外层查询对合并后的数据集按年代分组，汇总男女总数，并计算比例
3. 用CAST(... AS REAL)将整数转换为浮点型，确保比例计算不会被截断为整数

方案二：先分别分组再合并汇总（适合大数据量场景） #

如果两个表数据量很大，先各自分组统计再合并汇总可以减少数据传输量：

SELECT
    year_decade AS Year,
    SUM(male_total) AS "Sum of male",
    SUM(female_total) AS "Sum of female",
    CAST(SUM(male_total) AS REAL) / CAST(SUM(female_total) AS REAL) AS "Male/Female Ratio"
FROM (
    -- 先对table1按年代分组统计
    SELECT
        YEAR / 10 * 10 AS year_decade,
        SUM(CASE WHEN SEX LIKE 'Male%' THEN 1 ELSE 0 END) AS male_total,
        SUM(CASE WHEN SEX LIKE 'Female%' THEN 1 ELSE 0 END) AS female_total
    FROM table1
    WHERE YEAR IS NOT NULL
    GROUP BY year_decade
    UNION ALL
    -- 再对table2按年代分组统计
    SELECT
        YEAR / 10 * 10 AS year_decade,
        SUM(CASE WHEN SEX LIKE 'Male%' THEN 1 ELSE 0 END) AS male_total,
        SUM(CASE WHEN SEX LIKE 'Female%' THEN 1 ELSE 0 END) AS female_total
    FROM table2
    WHERE YEAR IS NOT NULL
    GROUP BY year_decade
) AS grouped_data
GROUP BY year_decade
ORDER BY year_decade;

注意事项： #

• 如果你的YEAR字段是字符串类型（比如存储为'2000'而非数值2000），需要先转换为数值再计算年代：CAST(YEAR AS INTEGER)/10*10
• 若性别字段存在其他值（比如非Male/Female开头的），可以根据需求调整CASE逻辑，比如添加ELSE 0确保计数准确

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