学习MySQL时嵌套查询报错：添加外层查询触发ER_PARSE_ERROR

问题描述 #

编写嵌套SQL查询时，内层子查询可正常运行，但添加最外层SELECT * FROM后触发语法错误，报错信息：

Error: ER_PARSE_ERROR: 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 'as main )' at line 26

添加外层查询是为了对结果执行更多操作。

错误代码 #

select * from 
(
select year,
  subs,
  unsubs,
  LAG(subs,1) OVER(ORDER BY year) as sub_py,
  LAG(unsubs,1) OVER(ORDER BY year) as unsub_py
  FROM 
  (
    (
      SELECT
      DATE_FORMAT(subscription_started,"%Y") as year,
      SUM(Case WHEN subscription_started is not null then 1 else 0 end) as subs
      FROM user_churn
      group by year
    ) as s
    INNER JOIN
    (
      SELECT
      DATE_FORMAT(subscription_ended,"%Y") as year_unsub,
      SUM(Case WHEN subscription_ended is not null then 1 else 0 end) as unsubs
      FROM user_churn
      group by year_unsub
    ) as us
      on s.year=us.year_unsub
  ) as main
)

问题原因 #

MySQL语法明确要求：FROM子句后的子查询必须指定别名，哪怕该别名不会被后续语句引用。你的代码中最外层的子查询（最后一个闭合括号）未添加别名，导致解析器无法识别该子查询的标识，触发语法错误。

修正后的代码 #

只需在最外层子查询的闭合括号后添加一个合法别名（示例用outer_result）即可：

select * from 
(
select year,
  subs,
  unsubs,
  LAG(subs,1) OVER(ORDER BY year) as sub_py,
  LAG(unsubs,1) OVER(ORDER BY year) as unsub_py
  FROM 
  (
    (
      SELECT
      DATE_FORMAT(subscription_started,"%Y") as year,
      SUM(Case WHEN subscription_started is not null then 1 else 0 end) as subs
      FROM user_churn
      group by year
    ) as s
    INNER JOIN
    (
      SELECT
      DATE_FORMAT(subscription_ended,"%Y") as year_unsub,
      SUM(Case WHEN subscription_ended is not null then 1 else 0 end) as unsubs
      FROM user_churn
      group by year_unsub
    ) as us
      on s.year=us.year_unsub
  ) as main
) as outer_result -- 新增子查询别名

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