mysql - SQL Union 全部抛出 SQL 语法错误，但两个 SELECT 没有问题

Question

我想连接 2 个 SELECT 语句。它们单独工作都很好，但如果我尝试使用 Union All，我会收到语法错误:

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 29

这对我来说没有任何意义。我搜索了该错误消息，但我没有在该行中使用引号(29 是 UNION ALL)

SELECT NULL                                                        AS id,
       table_a.calendar_day                             AS
       calendar_day,
       'XXX'                                                       AS mbr_entity
       ,
       'total'                                                    AS
       product,
       'total'                                                    AS
       mbr_stock_category,
       'total'                                                    AS component,
       SUM(IF(table_a.mbr_entity = 'XXX', aim, 0))      AS aim,
       SUM(IF(table_a.mbr_entity = 'XXX', cf, 0))       AS cf,
       SUM(IF(table_a.mbr_entity = 'XXX', mbr, 0))      AS mbr,
       SUM(IF(table_a.mbr_entity = 'XXX', ytd, 0))      AS ytd,
       SUM(IF(table_a.mbr_entity = 'XXX', forecast, 0)) AS forecast,
       SUM(table_b.a
           + table_b.b
           + table_b.c
           + table_b.d
           + table_b.e)                    AS actual
FROM   table_a
       left join table_b
              ON table_b.calendar_day =
                 table_a.calendar_day
                 AND table_b.mbr_entity =
                     table_a.mbr_entity
WHERE MONTH(table_a.calendar_day) = MONTH(CURRENT_DATE())
GROUP  BY calendar_day
UNION ALL
SELECT NULL                                                        AS id,
       table_a.calendar_day,
       table_a.mbr_entity,
       'total'                                                    AS product,
       'total'                                                    AS
       mbr_stock_category,
       'total'                                                    AS component,
       SUM(IF(table_a.mbr_entity = 'XXX', 0, aim))      AS aim,
       SUM(IF(table_a.mbr_entity = 'XXX', 0, cf))       AS cf,
       SUM(IF(table_a.mbr_entity = 'XXX', 0, mbr))      AS mbr,
       SUM(IF(table_a.mbr_entity = 'XXX', 0, ytd))      AS ytd,
       SUM(IF(table_a.mbr_entity = 'XXX', 0, forecast)) AS forecast,
       SUM(table_b.a
           + table_b.b
           + table_b.c
           + table_b.d
           + table_b.e)                    AS actual
FROM   table_a
       left join table_b
              ON table_b.calendar_day =
                 table_a.calendar_day
                 AND table_b.mbr_entity =
                     table_a.mbr_entity
WHERE  table_a.mbr_entity <> 'XXX'
AND MONTH(table_a.calendar_day) = MONTH(CURRENT_DATE())
GROUP  BY calendar_day,
          mbr_entity

Answer 1

再次尝试使用代码

( SELECT NULL AS
    id,
    table_a.calendar_day AS calendar_day,
    'XXX' AS mbr_entity,
    'total' AS product,
    'total' AS mbr_stock_category,
    'total' AS component,
    SUM( IF ( table_a.mbr_entity = 'XXX', aim, 0 ) ) AS aim,
    SUM( IF ( table_a.mbr_entity = 'XXX', cf, 0 ) ) AS cf,
    SUM( IF ( table_a.mbr_entity = 'XXX', mbr, 0 ) ) AS mbr,
    SUM( IF ( table_a.mbr_entity = 'XXX', ytd, 0 ) ) AS ytd,
    SUM( IF ( table_a.mbr_entity = 'XXX', forecast, 0 ) ) AS forecast,
    SUM( table_b.a + table_b.b + table_b.c + table_b.d + table_b.e ) AS actual 
    FROM
        table_a
        LEFT JOIN table_b ON table_b.calendar_day = table_a.calendar_day 
        AND table_b.mbr_entity = table_a.mbr_entity 
    WHERE
        MONTH ( table_a.calendar_day ) = MONTH ( CURRENT_DATE ( ) ) 
    GROUP BY
        calendar_day 
    ) 
    UNION ALL
    (
    SELECT NULL AS
        id,
        table_a.calendar_day,
        table_a.mbr_entity,
        'total' AS product,
        'total' AS mbr_stock_category,
        'total' AS component,
        SUM( IF ( table_a.mbr_entity = 'XXX', 0, aim ) ) AS aim,
        SUM( IF ( table_a.mbr_entity = 'XXX', 0, cf ) ) AS cf,
        SUM( IF ( table_a.mbr_entity = 'XXX', 0, mbr ) ) AS mbr,
        SUM( IF ( table_a.mbr_entity = 'XXX', 0, ytd ) ) AS ytd,
        SUM( IF ( table_a.mbr_entity = 'XXX', 0, forecast ) ) AS forecast,
        SUM( table_b.a + table_b.b + table_b.c + table_b.d + table_b.e ) AS actual 
    FROM
        table_a
        LEFT JOIN table_b ON table_b.calendar_day = table_a.calendar_day 
        AND table_b.mbr_entity = table_a.mbr_entity 
    WHERE
        table_a.mbr_entity <> 'XXX' 
        AND MONTH ( table_a.calendar_day ) = MONTH ( CURRENT_DATE ( ) ) 
    GROUP BY
        calendar_day,
    mbr_entity 
    )