SQLSTATE[42000]:语法错误或访问冲突:1055 SELECT 列表的表达式 #3 不在 GROUP BY 子句中并且包含非聚合

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated(SQLSTATE[42000]:语法错误或访问冲突:1055 SELECT 列表的表达式 #3 不在 GROUP BY 子句中并且包含非聚合)

问题描述

当我将 ubuntu 从 15.10 升级到 16.04 时，我的 yii2 项目中出现此错误

when i got upgraded my ubuntu from 15.10 to 16.04 i have this erro in my yii2 project

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3
of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'iicityYii.opportunity_conditions.money' which is not functionally dependent 
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

正在执行的 SQL 是:

The SQL being executed was:

SELECT SUM(oc.money),op.id,oc.money,
            op.mantaghe,
            op.`time`, op.`id`, `op`.`logo`,
           `pd`.`user_id`, `op`.`name`, 
           `pd`.`co_name`, `op`.`address`, 
           `op`.`project_type_id`, `op`.`state_id`
FROM `opportunity` op 
INNER JOIN `profile_details` pd  ON op.user_id=pd.user_id  
INNER JOIN `opportunity_conditions` oc ON   op.id=oc.opportunity_id
GROUP BY `op`.`id`
ORDER BY `op`.`id` DESC

如何解决我的问题?

推荐答案

在 select 中有一个名为 sum 的聚合函数和一组列名，错误提示您没有在 group 中指定正确的列名列表通过条款.可能是您应该在组中添加更多列名称，可能与 profile_details, chance_conditions 表

In select you have an aggregate function named sum and a set of column name, the error tell you that you have not specified the correct list of column name in group by clause. could be you should add more columns name in group by probably related to the profile_details, opportunity_conditions table

你还有 ,(opportunity.id),(opportunity_conditions.money), (opportunity.mantaghe), 为什么 () 如果你需要总和你必须加上对所有列求和

You have also ,(opportunity.id),(opportunity_conditions.money), (opportunity.mantaghe), why the () if you need sum you must add sum to all column

sum(opportunity.id), sum(opportunity_conditions.money),

sum(opportunity.mantaghe),

sum(opportunity.mantaghe),

否则，如果这是普通列，则应使用不带 () 的普通语法

otherwise if this are normal columns you should use the normal syntax without ()

opportunity.id, chance_conditions.money,opportunity.mantaghe,

我试图重写一个可能的查询

I have tried to rewrite a possible query

 SELECT SUM(opportunity_conditions.money),
        `opportunity`.`id`,
        `opportunity_conditions.money`,
        `opportunity.mantaghe`, 
        `opportunity`.`time`, 
        `opportunity`.`logo`, 
        `profile_details`.`user_id`,
        `opportunity`.`name`, 
        `profile_details`.`co_name`,
        `opportunity`.`address`, 
        `opportunity`.`project_type_id`,
        `opportunity`.`state_id` 
FROM `opportunity` 
INNER JOIN `profile_details` ON `opportunity`.`user_id`= `profile_details`.`user_id` 7
INNER JOIN `opportunity_conditions` ON `opportunity`.`id`=`opportunity_conditions`.`opportunity_id` 
GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  
ORDER BY `opportunity`.`id` DESC

在基本列名上使用 group by(我希望)

with group by on the essential column name (i hope)

GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  

这篇关于SQLSTATE[42000]:语法错误或访问冲突:1055 SELECT 列表的表达式 #3 不在 GROUP BY 子句中并且包含非聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！