如何解析 SELECT 列表不在 GROUP BY 子句中，并且包含非聚合

Question

我在这个请求上收到 MYSQL 5.7 的错误。如何解决此错误？

#1055 - SELECT list 的表达式 #3 不在 GROUP BY 子句中，并且包含非聚合列 'test.c.customers_group_id'，该列在功能上不依赖于 GROUP BY 子句中的列;这与 sql_mode=only_full_group_by 不兼容

select  SQL_CALC_FOUND_ROWS  c.customers_firstname, 
                             c.customers_lastname, 
                             c.customers_group_id,
                             sum(op.products_quantity * op.final_price) as ordersum 
from customers c,
     orders_products op,
     orders o
where c.customers_id = o.customers_id 
and o.orders_id = op.orders_id 
group by c.customers_firstname, 
         c.customers_lastname 
order by ordersum DESC

Answer 1

c.customers_group_id也包含在分组依据子句中

Answer 2

您错过了GROUP BY子句中的c.customers_group_id。

相反，您可以使用 ANSI JOIN模式使用旧式逗号分隔的表列表模式。

以下代码将适用于您的情况：

SELECT  SQL_CALC_FOUND_ROWS  c.customers_firstname, 
                             c.customers_lastname, 
                             c.customers_group_id,
                             sum(op.products_quantity * op.final_price) as ordersum 
FROM customers c
JOIN orders o ON o.customers_id = c.customers_id 
JOIN orders_products op ON op.orders_id = o.orders_id
GROUP BY c.customers_firstname, 
         c.customers_lastname,
         c.customers_group_id -- you missed this
ORDER BY ordersum DESC