MySQL多列子查询执行失败（Error 1055）问题咨询

报错原因 #

单独执行子查询时，SELECT YEAR(HIREDATE), MAX(SAL) FROM emp GROUP BY YEAR(HIREDATE)是符合only_full_group_by规则的——分组列YEAR(HIREDATE)和聚合函数MAX(SAL)的组合合法。但将其放入(YEAR(HIREDATE), SAL) IN (...)结构后，MySQL查询优化器会对整个语句进行重写（比如尝试转换为关联查询或HAVING子句形式），重写过程中误判YEAR(HIREDATE)为未在GROUP BY中声明的非聚合列，从而触发1055错误。

解决方法 #

1. 用派生表包装子查询

通过给子查询套一层派生表，强制MySQL先生成子查询的临时结果集，再进行IN匹配，规避优化器重写的问题：

SELECT ENAME, HIREDATE, SAL 
FROM emp
WHERE (YEAR(HIREDATE), SAL) IN (
  SELECT hire_year, max_sal
  FROM (
    SELECT YEAR(HIREDATE) AS hire_year, MAX(SAL) AS max_sal
    FROM emp
    GROUP BY YEAR(HIREDATE)
  ) AS temp
);

2. 使用JOIN替代IN子查询（推荐）

JOIN方式不仅能避免优化器的误判，性能通常更优，逻辑也更直观：

SELECT e.ENAME, e.HIREDATE, e.SAL
FROM emp e
INNER JOIN (
  SELECT YEAR(HIREDATE) AS hire_year, MAX(SAL) AS max_sal
  FROM emp
  GROUP BY YEAR(HIREDATE)
) t 
ON YEAR(e.HIREDATE) = t.hire_year AND e.SAL = t.max_sal;

3. 临时关闭only_full_group_by（不推荐）

仅适合临时测试场景，长期使用会违反SQL标准，可能导致其他查询结果异常：

-- 临时移除ONLY_FULL_GROUP_BY
SET sql_mode = REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', '');

-- 执行目标查询
SELECT ENAME, HIREDATE, SAL 
FROM emp
WHERE (YEAR(HIREDATE), SAL) IN (
  SELECT YEAR(HIREDATE), MAX(SAL)
  FROM emp
  GROUP BY YEAR(HIREDATE)
);

-- 恢复原有sql_mode（可选）
SET sql_mode = CONCAT('ONLY_FULL_GROUP_BY,', @@sql_mode);

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