1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

放到my.ini 的mysqld配置中

今天遇到个很迷的问题，先上代码：

<?php
//获取用户设备最新登录结果集
        $loginLogQuery = (new Query())
            ->select(['user_id', 'device_id', 'created_at'])
            ->from(LoginLog::tableName())
            ->groupBy(['user_id', 'device_id'])  //注意这个groupBy中的字段，必须得与上面select的字段保持一致，否则可能会出现由sql_mode引起的group by问题
            ->orderBy(['created_at' => SORT_DESC]);
        $result = array();
        // 创建查询器
        $query = new Query();
        $query->select([
            'uad.nick',
            'u.user_name',
            'ude.user_device_name',
            'ude.id',
            'ude.user_id',
            'ude.user_device_type',
            'ude.created_at AS dcat',
            'ude.user_device_info',
            'q.created_at AS login_at'
        ])
            ->from(['ude' => UserDevices::tableName()])
            ->leftJoin(['u' => User::tableName()], 'ude.user_id = u.id')
            ->leftJoin(['uad' => UserAdditionals::tableName()], 'u.id = uad.user_id')
            ->leftJoin(['q' => $loginLogQuery], 'q.user_id=ude.user_id AND q.device_id=ude.id')
            ->where(['u.domain_id' => $params['domain_id']]);
        if (!empty($params['type'])) {
            $query->andWhere('ude.user_device_type = :type', [':type' => $params['type']]);
        }
        $result['count'] = $query->count();

然后运行时报错：
Exception (Database Exception) ‘yii\db\Exception’ with message ‘SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘smy_20190731.simiyun_login_log.created_at’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The SQL being executed was: SELECT COUNT(*) AS count FROM (SELECT uad.nick, u.user_name, ude.user_device_name, ude.id, ude.user_id, ude.user_device_type, ude.created_at AS dcat, ude.user_device_info, q.created_at AS login_at FROM simiyun_user_devices ude LEFT JOIN simiyun_users u ON ude.user_id = u.id LEFT JOIN simiyun_user_additionals uad ON u.id = uad.user_id LEFT JOIN (SELECT user_id, device_id, created_at FROM simiyun_login_log GROUP BY user_id, device_id ORDER BY created_at DESC) q ON q.user_id=ude.user_id AND q.device_id=ude.id WHERE u.domain_id=1) s‘

网上查询得知是由于数据库设置引起sql-mode里的ONLY_FULL_GROUP_BY字段导致不能直接查询group_by包裹的之外的字段 解决mysql的配置ONLY_FULL_GROUP_BY引起的错误

但做项目改配置的话也不太合适。

不过最后通过 https://www.imooc.com/qadetail/249114?t=408465 里面的回答得到了灵感。两种解决方法：
1，select和groupby字段保持一致。但这个和想达到的有点远

<?php
$loginLogQuery = (new Query())
            ->select(['user_id', 'device_id', 'created_at'])
            ->from(LoginLog::tableName())
            ->groupBy(['user_id', 'device_id', 'created_at'])//注意这个groupBy中的字段，必须得与上面select的字段保持一致，否则可能会出现由sql_mode引起的group by问题
            ->orderBy(['created_at' => SORT_DESC]);

2，不在groupby中的字段保持唯一就行

<?php
$loginLogQuery = (new Query())
            ->select(['user_id', 'device_id', 'MAX(created_at) AS created_at'])
            ->from(LoginLog::tableName())
            ->groupBy(['user_id', 'device_id', 'created_at'])//注意这个groupBy中的字段，必须得与上面select的字段保持一致，否则可能会出现由sql_mode引起的group by问题
            ->orderBy(['created_at' => SORT_DESC]);