TP5使用group报错：1055 Expression #1 of SELECT list is not in GROUP

appleคิดถึง

已于 2024-04-13 15:59:10 修改

阅读量444

点赞数 1

分类专栏： tp5 文章标签： group tp5 php

于 2024-04-13 15:29:11 首次发布

本文链接：https://blog.csdn.net/weixin_43652106/article/details/137715343

版权

tp5 专栏收录该内容

41 篇文章 0 订阅

订阅专栏

本文讲述了在将Mysql视图从线上迁移到本地后遇到的Syntaxerror或Accessviolation:1055错误，主要介绍了两种解决方案：一是通过修改my.cnf或mysql.ini文件调整SQL_mode；二是修改SQL语句，使用any_value()函数处理非groupby列。

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

使用group报错

Mysql环境是5.7的, 使用了View进行了表连接, 进行了表连接

搬迁到本地后, 查询报错 Syntax error or access violation: 1055 Expression

解决方法1

配置 my.cnf(linux)文件 win下面是 mysql.ini文件

在 mysqld 里加上

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

重启mysql即可

解决方法2

在不修改MySQL配置文件的情况下，需要修改sql语句来执行。

group by后面的列名，还是和以前一样通过select直接获取，而对于select中获取非group by的信息，则要通过any_value()函数。
例如：

// 筛选商家服务范围包含当前选择小区的商家
$subQuery  = FuwuModel::where(function ($query) use ($houseRow){
            $query->where(function ($query) use ($houseRow){
                $query->where(['level' => 'province','province_id' => $houseRow['province_id']]);
            })->whereor(function ($query) use ($houseRow){
                $query->where(['level' => 'city','province_id' => $houseRow['province_id'],'city_id' => $houseRow['city_id']]);
            })->whereor(function ($query) use ($houseRow){
                $query->where(['level' => 'district','province_id' => $houseRow['province_id'],'city_id' => $houseRow['city_id'],'district_id' => $houseRow['district_id']]);
            })->whereor(function ($query) use ($houseRow){
                $query->where(['level' => 'street','province_id' => $houseRow['province_id'],'city_id' => $houseRow['city_id'],'district_id' => $houseRow['district_id'],'street_id' => $houseRow['street_id']]);
            })->whereor(function ($query) use ($houseRow){
                $query->where(['level' => 'house','province_id' => $houseRow['province_id'],'city_id' => $houseRow['city_id'],'district_id' => $houseRow['district_id'],'street_id' => $houseRow['street_id'],'house' => $houseRow['houseid']]);
            });
        })->field("any_value(shop_fuwu_id) as shop_fuwu_id,shop_id")->group('shop_id')->buildSql();

$top_shop = $manystoreShop
    ->alias('m')
    ->join("$subQuery f", 'm.id = f.shop_id')
    ->where('m.shop_status','10')
    ->where('m.shop_tab','1')
    ->field('id,logo,songdatag_id,name')
    ->order('weigh desc')                        
    ->limit(5)
    ->select();