＃1055-选择列表的表达式＃6不在clob by子句中，而包含非聚集列

Question

我在MySQL 5.7中的查询有问题，但是在MySQL 5.6中，它运行良好。

每次都会出现此消息：

1055-选择列表的表达式＃6不在组中，并包含非聚集列
'electricity_databases.electricity_invoices.date_inserted'，它在coult by Clause中的列中并不取决于子句中的列；这与sql_mode = hell

SQL代码：

SELECT 
homes.id,
homes.homeName,
homes.city, 
homes.date_registered,
ROUND(SUM(electricity_invoices.total), 2) AS TotalPrice,
DATEDIFF(NOW(), electricity_invoices.date_inserted) AS last_insert_in_days,
MAX(electricity_invoices.date_inserted) AS last_insert,
COUNT(electricity_invoices.homeID) AS countPaymentTimes,
MAX(electricity_invoices.currRead) AS currRead,
MAX(electricity_invoices.prevRead) AS prevRead,
ROUND(MAX(electricity_invoices.currRead) - MAX(electricity_invoices.prevRead), 1) AS lastComp,
customer.name
FROM homes
LEFT JOIN electricity_invoices ON
homes.id = electricity_invoices.homeID
LEFT JOIN customer ON
homes.id = customer.homeID
GROUP BY homes.id
ORDER BY homes.id

Answer 1

原因是，在mysql的最新版本中，默认情况下，不允许不允许按子句中的组中添加非聚合列。您可以通过模式从完整组禁用SQL_Mode来禁用此行为。

只需在group by子句中添加非聚集列。

select homes.id,
    homes.homeName,
    homes.city,
    homes.date_registered,
    ROUND(SUM(electricity_invoices.total), 2) as TotalPrice,
    DATEDIFF(NOW(), electricity_invoices.date_inserted) as last_insert_in_days,
    MAX(electricity_invoices.date_inserted) as last_insert,
    COUNT(electricity_invoices.homeID) as countPaymentTimes,
    MAX(electricity_invoices.currRead) as currRead,
    MAX(electricity_invoices.prevRead) as prevRead,
    ROUND(MAX(electricity_invoices.currRead) - MAX(electricity_invoices.prevRead), 1) as lastComp,
    customer.name
from homes
left join electricity_invoices on homes.id = electricity_invoices.homeID
left join customer on homes.id = customer.homeID
group by homes.id,
    homes.homeName,
    homes.city,
    homes.date_registered,
    customer.name

Answer 2

开始MySQL 5.7您可以选择组中不存在的非聚合列如果您想要以前版本的相同行为或更正确地，您应该通过所有列未汇总的列来建立选择或分组例如：

SELECT 
homes.id,
homes.homeName,
homes.city, 
homes.date_registered,
ROUND(SUM(electricity_invoices.total), 2) AS TotalPrice,
DATEDIFF(NOW(), electricity_invoices.date_inserted) AS last_insert_in_days,
MAX(electricity_invoices.date_inserted) AS last_insert,
COUNT(electricity_invoices.homeID) AS countPaymentTimes,
MAX(electricity_invoices.currRead) AS currRead,
MAX(electricity_invoices.prevRead) AS prevRead,
ROUND(MAX(electricity_invoices.currRead) - MAX(electricity_invoices.prevRead), 1) AS lastComp,
customer.name
FROM homes
LEFT JOIN electricity_invoices ON
homes.id = electricity_invoices.homeID
LEFT JOIN customer ON
homes.id = customer.homeID
GROUP BY homes.id, homes.homeName,homes.city, homes.date_registered, DATEDIFF(NOW(), electricity_invoices.date_inserted) AS last_insert_in_days
ORDER BY homes.id

或，由于您不需要这些列的特定值，请使用（假）聚合这些列

SELECT 
homes.id,
min(homes.homeName),
min(homes.city), 
min(homes.date_registered),
ROUND(SUM(electricity_invoices.total), 2) AS TotalPrice,
min(DATEDIFF(NOW(), electricity_invoices.date_inserted) AS last_insert_in_days),
MAX(electricity_invoices.date_inserted) AS last_insert,
COUNT(electricity_invoices.homeID) AS countPaymentTimes,
MAX(electricity_invoices.currRead) AS currRead,
MAX(electricity_invoices.prevRead) AS prevRead,
ROUND(MAX(electricity_invoices.currRead) - MAX(electricity_invoices.prevRead), 1) AS lastComp,
customer.name
FROM homes
LEFT JOIN electricity_invoices ON
homes.id = electricity_invoices.homeID
LEFT JOIN customer ON
homes.id = customer.homeID
GROUP BY homes.id
ORDER BY homes.id