mybatis的group by有关的未解决问题

最新推荐文章于 2023-08-29 17:25:40 发布

鼻子亲了脸

最新推荐文章于 2023-08-29 17:25:40 发布

阅读量1.1k

点赞数

本文链接：https://blog.csdn.net/weixin_51948450/article/details/113632570

版权

本文探讨了在mybatis中使用group by时遇到的问题，主要集中在连表查询与Mysql的区别。当attr_id和attr_name存在一一对应关系时，condition2能够正常运行，而condition1却导致错误。

摘要由CSDN通过智能技术生成

mybatis的group by有关的问题

连表查询中，attr_id和attr_name是一一对应的关系

#Condition1:GROUP BY pssav.`attr_id`
SELECT pssav.`attr_id`,pssav.`attr_name`,GROUP_CONCAT(DISTINCT pssav.`attr_value`) 
FROM `pms_sku_info` info 
LEFT JOIN `pms_sku_sale_attr_value` pssav ON info.`sku_id`=pssav.`sku_id`
WHERE info.`spu_id`=4
GROUP BY pssav.`attr_id`,pssav.`attr_name`

#Condition2:GROUP BY pssav.`attr_id`,pssav.`attr_name`;
SELECT pssav.`attr_id`,pssav.`attr_name`,GROUP_CONCAT(DISTINCT pssav.`attr_value`) 
FROM `pms_sku_info` info 
LEFT JOIN `pms_sku_sale_attr_value` pssav ON info.`sku_id`=pssav.`sku_id`
WHERE info.`spu_id`=4
GROUP BY pssav.`attr_id`,pssav.`attr_name`;

在Mysql中查询为一样的结果，但是在在mybatis中写sql语句时，condition2可以正常运行，但是condition1会报错

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'taomall_pms.pssav.attr_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
### The error may exist in file [D:\workspace\java\taomall\taomall-product\target\classes\mapper\product\SkuSaleAttrValueDao.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT             pssav.`attr_id` attr_id,             pssav.`attr_name` attr_name,             GROUP_CONCAT(DISTINCT pssav.`attr_value`) attr_values             FROM `pms_sku_info` info         LEFT JOIN `pms_sku_sale_attr_value` pssav ON info.`sku_id`=pssav.`sku_id`         WHERE info.`spu_id`=?         GROUP BY pssav.`attr_id`;
### Cause: java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'taomall_pms.pssav.attr_name' 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 java.sql.SQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'taomall_pms.pssav.attr_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractF