這個sql語句違背了sql_mode=only_full_group_by 。

先看看這個sql_mode=only_full_group_by是個什么意思:

(摘自官網文檔)

ONLY_FULL_GROUP_BY

Reject?queries?for?which?the?select?list,?HAVING?condition,?or?ORDER?BY?list?refer?to?nonaggregated?columns?that?are?neither?named?in?the?GROUP?BYclause?nor?are?functionally?dependent?on?(uniquely?determined?by)?GROUP?BY?columns.

As?of?MySQL?5.7.5,?the?default?SQL?mode?includes?ONLY_FULL_GROUP_BY.?(Before?5.7.5,?MySQL?does?not?detect?functional?dependency?andONLY_FULL_GROUP_BY?is?not?enabled?by?default.?For?a?description?of?pre-5.7.5?behavior,?see?the?MySQL?5.6?Reference?Manual.)

A?MySQL?extension?to?standard?SQL?permits?references?in?the?HAVING?clause?to?aliased?expressions?in?the?select?list.?Before?MySQL?5.7.5,?enablingONLY_FULL_GROUP_BY?disables?this?extension,?thus?requiring?the?HAVING?clause?to?be?written?using?unaliased?expressions.?As?of?MySQL?5.7.5,?this?restriction?is?lifted?so?that?the?HAVING?clause?can?refer?to?aliases?regardless?of?whether?ONLY_FULL_GROUP_BY?is?enabled.

For?additional?discussion?and?examples,?see?Section?13.20.3,?“MySQL?Handling?of?GROUP?BY”.

來源鏈接:

http://dev.MySQL.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

解決辦法:

set?@@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

去掉ONLY_FULL_GROUP_BY即可正常執(zhí)行sql.