错误代码:1055与sql_mode = only_full_group_by不兼容 [英] Error Code: 1055 incompatible with sql_mode=only_full_group_by

问题描述

我一直在切换到拉曼SQL棒球数据库的脱机版本时遇到问题.我正在使用嵌入到EDX课程中的终端.此命令可以在Web终端上正常运行:

I have been having issues switching to an offline version of the Lahman SQL baseball database. I was using a terminal embed into an EDX course. This command runs fine on the web terminal:

SELECT concat(m.nameFirst,concat(" ",m.nameLast)) as Player,
    p.IPOuts/3 as IP,
    p.W,p.L,p.H,p.BB,p.ER,p.SV,p.SO as K,
    p.IPOuts+p.W*5+p.SV+p.SO-p.BB-p.L-p.H as PTS,
    p.yearID as Year
FROM Pitching p
Inner Join Master m
    ON p.playerID=m.playerID
WHERE p.yearID=2014 AND p.IPOuts>=50
GROUP BY m.playerID
ORDER BY PTS DESC;

哪个正在运行SQL 5.5.46，但是当我使用运行5.7.10的脱机版本时，出现以下错误代码:

Which is running SQL 5.5.46, but when I use my offline version running 5.7.10 I get the following error code:

错误代码:1055.SELECT列表的表达式#1不在GROUP BY中 子句，其中包含未汇总的列"stats.m.nameFirst"，即 在功能上不依赖于GROUP BY子句中的列；这是 与sql_mode = only_full_group_by

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'stats.m.nameFirst' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我一直在阅读许多解决人们问题的解决方案，但在这种情况下他们没有帮助.这从来没有发生过，所以我认为这是非常明显的，或者我对编码的了解还不错.无论如何，有人知道如何解决此问题吗?

I've been reading a lot of solutions to people's problems, but they haven't helped in this case. That's never happened before, so I think this is either super obvious or maybe I'm getting ok at coding. Anyway, anyone know how to fix this?

推荐答案

在5.7中，sqlmode默认设置为:

In 5.7 the sqlmode is set by default to:

 ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

要删除子句ONLY_FULL_GROUP_BY，您可以执行以下操作:

To remove the clause ONLY_FULL_GROUP_BY you can do this:

SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

这表明您需要使用未聚合的列来创建GROUP BY.

This supposed you need to make that GROUP BY with non aggregated columns.

致谢

这篇关于错误代码:1055与sql_mode = only_full_group_by不兼容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！