mysql 左连接 group by_mysql – A. *不在GROUP BY中,并且在laravel查询构建器上具有左连接...

$search_alls=

DB::table('a16s as A')

->select('A.id')

// ->select('A.*')

->addSelect(DB::raw('SUM(CASE WHEN B.approve = 1 ELSE 0 END) as Yshow'))

->leftjoin('a16s_likes as B', function($join) {

$join->on('A.id', '=', 'B.p_id');

})

->groupBy('A.id')

->get();

当我在上面使用select(‘A.id’)时效果很好.

但是当我使用select(‘A.*’)来选择所有A cloumn时

我收到了错误

SQLSTATE[42000]: Syntax error or access violation: 1055 'employee.A.name' isn't in GROUP BY

PS：员工是我的DB名称

A表上的列是

id name ....

1 john

2 mary

3 susan

如何通过leftjoin选择所有列？

列A.id是与B.p_id列的一对多关系.

解决方法:

要解决此问题,您需要在select list和group by子句中指定必需的列

$search_alls=DB::table('a16s as A')

->select('A.id','A.name')

->addSelect(DB::raw('SUM(CASE WHEN B.approve = 1 ELSE 0 END) as Yshow'))

->leftjoin('a16s_likes as B', function($join) {

$join->on('A.id', '=', 'B.p_id');

})

->groupBy('A.id')

->groupBy('A.name');

->get();

As per newer release mysql 5.7 does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause

按照文档

MySQL 5.7.5 and up implements detection of functional dependence. If the 07001 SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and 07001 is not enabled by default

标签：laravel,mysql,group-by,laravel-query-builder,left-join

来源： https://codeday.me/bug/20190929/1833482.html