Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘pandaria.variant.price’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by↵; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘pandaria.variant.price’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"
在用mysql做一个统计一个产品的销售情况,需要从订单表查询每个产品的价格(price)和数量(quantity)以及从产品表查询产品的sku,产品ID等信息。
我在查询的时候只用了产品ID做group by分组查询,这个最开始运行程序是没问题的,后来后台改了产品的价格导致先前的订单A与后面生成的订单B产品价格不一样了,在用group by查询就出现了这个错误。因此此时仅依靠产品ID分组是不对的,还要根据每个订单产品的价格分组查询才行
修改前
select p.id,p.sku, o.price,o.quantity,sum(o.price * o.quantity)
from orders o
inner join products p
on o.product_id = p.id
where o.id = '1'
group by p.id
修改后
select p.id,p.sku, o.price,o.quantity,sum(o.price * o.quantity)
from orders o
inner join products p
on o.product_id = p.id
where o.id = '1'
group by p.id,o.price
在使用group by查询的时候本来是应该一个产品对应一条数据,如果产品价格更改过一个产品ID就会对应多条o.price数据,这个问题很简单,这里犯的错也很低级。在设计sql使用到group by的时候一定要把所有的可能会发生改变并且不参与聚合运算的的数据都添加在group by 后面