MySQL派生表全部已设别名，为何仍报1248必须设置别名错误？

问题原因 #

你提交的SQL语句语法层面所有派生表（n1、n2、subquery）都已配置合法别名，出现该报错是低版本MySQL的字段引用识别机制导致的：当外部查询直接引用嵌套派生表的字段、未明确指定所属表别名时，MySQL会误判存在未命名的派生表，触发1248错误。

解决方案 #

将SELECT和GROUP BY子句中来自subquery派生表的字段，明确加上subquery.前缀即可，修正后的完整语句如下：

SELECT DATE_FORMAT(p.date_admitted, '%Y-%m') as month_admitted,
  subquery.diagnosis as diagnosis,
  subquery.education as education,
  COUNT(*) as total 
FROM patient_discharge_form d 
INNER JOIN survey_data sd ON sd.id = d.id 
LEFT JOIN submission s ON s.id = sd.submission_id 
LEFT JOIN patient p ON p.id = s.patient_id 
LEFT JOIN (
  SELECT n1.id,n1.diagnosis,n2.education
  FROM (
          SELECT id,'Gest Hyp' as diagnosis FROM patient_discharge_form WHERE gestational_hypertension=1
          UNION ALL
          SELECT id,'Pre w/ Sev' FROM patient_discharge_form WHERE preeclampsia_non_severe=1
        ) n1 
  INNER JOIN (
          SELECT id,'Written' as education FROM patient_discharge_form WHERE education LIKE '%written%'
          UNION ALL
          SELECT id,'Verbal' FROM patient_discharge_form WHERE education LIKE '%verbal%'
        ) n2 ON n1.id=n2.id
) subquery ON d.id = subquery.id 
WHERE (s.status = 'complete') 
GROUP BY month_admitted, subquery.diagnosis, subquery.education

如果调整后仍有报错，MySQL 8.0及以上版本可以将嵌套派生表改写为公用表表达式（CTE）进一步降低识别歧义。

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