条件式UNION语法错误求助：如何实现带条件的UNION拼接？

当然可以实现你想要的条件式UNION逻辑！我给你两种实用方案，一种更简洁易维护，另一种完全贴合你最初的思路：

方案一：用LEFT JOIN补全缺失行（推荐） #

这种方法不需要写复杂的条件判断，而是先构建一个包含所有你需要的基准组合（月份、cor、对应名称和排序值）的数据集，再和你的聚合查询结果做LEFT JOIN，自动把缺失的组合补全并将计数设为0。逻辑清晰，扩展性强，以后要加其他月份或cor值直接修改基准数据即可：

WITH base_data AS (
    SELECT 1 AS mes, 'VD' AS cor, 'Janeiro' AS mescor, '1' AS expr2
    UNION ALL SELECT 1, 'AM', 'Janeiro', '2'
    UNION ALL SELECT 1, 'VM', 'Janeiro', '3'
)
SELECT 
    bd.mes,
    bd.cor,
    bd.mescor,
    COALESCE(t.Expr1, 0) AS Expr1, -- 把NULL的计数替换成0
    bd.expr2
FROM base_data bd
LEFT JOIN (
    -- 你的原聚合查询部分
    SELECT 
        MONTH(data) AS MES, 
        cor, 
        CASE MONTH(data) 
            WHEN 1 THEN 'Janeiro' 
            WHEN 2 THEN 'Fevereiro' 
            WHEN 3 THEN 'Março' 
            WHEN 4 THEN 'Abril' 
            WHEN 5 THEN 'Maio' 
            WHEN 6 THEN 'Junho' 
            WHEN 7 THEN 'Julho' 
            WHEN 8 THEN 'Agosto' 
            WHEN 9 THEN 'Setembro' 
            WHEN 10 THEN 'Outubro' 
            WHEN 11 THEN 'Novembro' 
            WHEN 12 THEN 'Dezembro' 
        END AS MESCOR, 
        COUNT(*) AS Expr1, 
        CASE cor 
            WHEN 'AM' THEN '2' 
            WHEN 'VD' THEN '1' 
            WHEN 'VM' THEN '3' 
        END AS Expr2 
    FROM TBINICIATIVAS_PREVISTAS 
    WHERE login ='xxxxxxx' 
    GROUP BY MONTH(data), cor
) t 
    ON bd.mes = t.mes 
    AND bd.cor = t.cor 
    AND bd.mescor = t.mescor 
    AND bd.expr2 = t.expr2
ORDER BY bd.expr2, bd.mes;

方案二：贴合你思路的条件式UNION ALL #

如果你坚持要实现“仅当计数为0时才拼接SELECT”的逻辑，可以用UNION ALL配合NOT EXISTS来实现。注意用UNION ALL代替UNION，避免不必要的去重操作，提升性能：

-- 你的原聚合查询结果
SELECT 
    MONTH(data) AS MES, 
    cor, 
    CASE MONTH(data) 
        WHEN 1 THEN 'Janeiro' 
        WHEN 2 THEN 'Fevereiro' 
        WHEN 3 THEN 'Março' 
        WHEN 4 THEN 'Abril' 
        WHEN 5 THEN 'Maio' 
        WHEN 6 THEN 'Junho' 
        WHEN 7 THEN 'Julho' 
        WHEN 8 THEN 'Agosto' 
        WHEN 9 THEN 'Setembro' 
        WHEN 10 THEN 'Outubro' 
        WHEN 11 THEN 'Novembro' 
        WHEN 12 THEN 'Dezembro' 
    END AS MESCOR, 
    COUNT(*) AS Expr1, 
    CASE cor 
        WHEN 'AM' THEN '2' 
        WHEN 'VD' THEN '1' 
        WHEN 'VM' THEN '3' 
    END AS Expr2 
FROM TBINICIATIVAS_PREVISTAS 
WHERE login ='xxxxxxx' 
GROUP BY MONTH(data), cor

-- 仅当VD组合无数据时添加该行
UNION ALL
SELECT '1', 'VD', 'Janeiro', 0, '1'
WHERE NOT EXISTS (
    SELECT 1 
    FROM TBINICIATIVAS_PREVISTAS 
    WHERE login ='xxxxxxx' 
      AND MONTH(data) = 1 
      AND cor = 'VD'
)

-- 仅当AM组合无数据时添加该行
UNION ALL
SELECT '1', 'AM', 'Janeiro', 0, '2'
WHERE NOT EXISTS (
    SELECT 1 
    FROM TBINICIATIVAS_PREVISTAS 
    WHERE login ='xxxxxxx' 
      AND MONTH(data) = 1 
      AND cor = 'AM'
)

-- 仅当VM组合无数据时添加该行
UNION ALL
SELECT '1', 'VM', 'Janeiro', 0, '3'
WHERE NOT EXISTS (
    SELECT 1 
    FROM TBINICIATIVAS_PREVISTAS 
    WHERE login ='xxxxxxx' 
      AND MONTH(data) = 1 
      AND cor = 'VM'
)

ORDER BY expr2, mes;

注意事项 #

• 如果你使用的是MySQL（版本低于8.0），不支持WITH子句，那可以把base_data改成子查询的形式。
• 注意MONTH(data)的返回类型是数字，你的原SQL里用了字符串'1'，如果字段类型不匹配可以调整成一致的。
• 替换login ='xxxxxxx'为实际的参数时，尽量用参数化查询避免SQL注入。

内容的提问来源于stack exchange，提问作者Charlotte T Cobucci