MySQL使用含MIN()的子查询报1064语法错误问题咨询

MySQL 语法强制要求 FROM 关键字后跟随的派生表（嵌套子查询的结果集）必须设置唯一别名，而 SQLite 无此强制校验规则，这是同类语句跨数据库迁移后报错的核心原因。

所有嵌套子查询作为派生表使用时，在子查询闭合的右括号后添加自定义别名即可。

你提供的业务查询修复后代码 #

SELECT ata, description, min(duedate) as duedate, tbocalendar, tbocalendar2, table_name 
FROM (
    SELECT ata, description, duedate1 AS duedate, tbocalendar, tbocalendar2, 'part_log' as table_name 
    FROM part_log 
    WHERE tbocalendar!= 'N/A' AND removed = 'FALSE' AND activecheck = 'ACTIVE' AND immatriculation=? 
    UNION 
    SELECT ata, description, duedate2 AS duedate, tbocalendar, tbocalendar2, 'part_log' as table_name 
    FROM part_log 
    WHERE removed = 'FALSE' AND activecheck = 'ACTIVE' AND immatriculation=? 
    UNION 
    SELECT ata, element as description, echeance AS duedate, tbocalendar, 'N/A' as tbocalendar2, 'ltr_log' as table_name  
    FROM ltr_log  
    WHERE date_cloture = 'N/A' AND echeance !='N/A' AND immatriculation=?
) AS temp_table -- 新增派生表别名，名称可自定义
WHERE substr(description,1,5)!='Carte'
GROUP BY ata, description, tbocalendar, tbocalendar2, table_name -- 补充聚合分组规则，避免MySQL开启ONLY_FULL_GROUP_BY模式时报错
LIMIT 1

其他同类语句修复示例 #

• 原语句1：Select xxx,yyyy from (Select xxx,yyyy from table_name union all Select xxx,yyyy from table_name1)
  修复后：Select xxx,yyyy from (Select xxx,yyyy from table_name union all Select xxx,yyyy from table_name1) AS t
• 原语句2：Select MIN(date_example) FROM (SELECT date1 as date_example from table1 UNION ALL SELECT date2 as date_example from table2)
  修复后：Select MIN(date_example) FROM (SELECT date1 as date_example from table1 UNION ALL SELECT date2 as date_example from table2) AS t

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