mysql - 等效查询对不同的表抛出错误：语法错误或访问冲突：1055 SELECT 列表的表达式 #2 不在 GROUP BY 中

Question

我有点困惑，为什么其中一个查询会引发错误，因为它们看起来基本相同。

工作查询：

select `name`, (select sum(quantity) from sales_channel_order_line_item where location_id = catalog_location.id) as aggregate from `catalog_location` group by `name` having `aggregate` > 0 order by `aggregate` desc limit 10

非工作查询：

select `name`, (select sum(quantity) from sales_channel_order_line_item where product_id = sales_channel_product.id) as aggregate from `sales_channel_product` group by `name` having `aggregate` > 0 order by `aggregate` desc limit 10

错误：

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sales_channel_product.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `name`, (select sum(quantity) from sales_channel_order_line_item where product_id = sales_channel_product.id) as aggregate from `sales_channel_product` group by `name` having `aggregate` > 0 order by `aggregate` desc limit 10)

这里有什么区别？

标签： mysql

Answer 1

我通过使用连接解决了这个问题：

select `catalog_location`.`name`, sum(quantity) as aggregate from `sales_channel_order_line_item` inner join `catalog_location` on `location_id` = `catalog_location`.`id` group by `catalog_location`.`name` order by `aggregate` desc limit 10