我想加入两个select语句。它们各自都工作得很好,但如果我尝试使用union all,就会出现语法错误:
[42000][1064]您的sql语法有错误;请查看与mysql服务器版本对应的手册,以获取第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
1条答案
按热度按时间mrfwxfqh1#
再次尝试使用代码