MySQL 8中基于VARCHAR字段的两个视图全连接报错及计算问题

1. FULL JOIN报错的原因 #

MySQL 不支持FULL OUTER JOIN语法，这是你执行语句报错的直接原因。左/右连接能正常运行是因为MySQL原生支持这两种连接类型，但全连接需要手动模拟实现。

2. 模拟全连接的两种可行方案 #

方案一：LEFT JOIN + UNION RIGHT JOIN #

通过UNION合并左连接和右连接的结果，自动去重（若需保留重复可改用UNION ALL，此处因ym为唯一标识，用UNION即可）：

SELECT 
    inC.ym, 
    inC.ms AS income_ms, 
    inC.quarter AS income_quarter, 
    outG.ms AS outcome_ms, 
    outG.quarter AS outcome_quarter
FROM income_month_year inC
LEFT JOIN outcome_month_year outG ON inC.ym = outG.ym
UNION
SELECT 
    outG.ym, 
    inC.ms AS income_ms, 
    inC.quarter AS income_quarter, 
    outG.ms AS outcome_ms, 
    outG.quarter AS outcome_quarter
FROM income_month_year inC
RIGHT JOIN outcome_month_year outG ON inC.ym = outG.ym;

方案二：先获取所有唯一月份，再左连双视图 #

先通过UNION获取两个视图中所有不重复的ym值作为基础表，再分别左连收入、支出视图，确保所有月份都被覆盖：

WITH all_months AS (
    SELECT ym FROM income_month_year
    UNION
    SELECT ym FROM outcome_month_year
)
SELECT 
    am.ym,
    inc.ms AS income_amount,
    inc.quarter AS income_quarter,
    outg.ms AS outcome_amount,
    outg.quarter AS outcome_quarter
FROM all_months am
LEFT JOIN income_month_year inc ON am.ym = inc.ym
LEFT JOIN outcome_month_year outg ON am.ym = outg.ym;

注：若你的MySQL版本低于8.0，不支持CTE（WITH语句），可将all_months替换为子查询：

SELECT 
    am.ym,
    inc.ms AS income_amount,
    inc.quarter AS income_quarter,
    outg.ms AS outcome_amount,
    outg.quarter AS outcome_quarter
FROM (
    SELECT ym FROM income_month_year
    UNION
    SELECT ym FROM outcome_month_year
) am
LEFT JOIN income_month_year inc ON am.ym = inc.ym
LEFT JOIN outcome_month_year outg ON am.ym = outg.ym;

3. 数值计算的实现 #

完全可以基于上述连接结果实现各类数值计算，比如每月收支差额、季度收支汇总等。以下是计算每月收支差额的示例：

WITH all_months AS (
    SELECT ym FROM income_month_year
    UNION
    SELECT ym FROM outcome_month_year
)
SELECT 
    am.ym,
    COALESCE(inc.ms, 0) AS income_amount,
    COALESCE(outg.ms, 0) AS outcome_amount,
    COALESCE(inc.ms, 0) - COALESCE(outg.ms, 0) AS monthly_balance
FROM all_months am
LEFT JOIN income_month_year inc ON am.ym = inc.ym
LEFT JOIN outcome_month_year outg ON am.ym = outg.ym;

这里用COALESCE函数将NULL值替换为0，避免计算时出现无效的NULL结果。

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