MYSQL，GROUP BY子句；这与sql_mode=only_full_group_by[duplicate]不兼容

Question

下面的CodeIgniter查询给出了一个错误提示；

SELECT列表的表达式#22不在GROUP BY子句中，并且包含非聚合列'HW3.1.HW_COMABETY.ID'，该列在功能上不依赖于GROUP BY子句中的列；这与sql_mode=only_full_group_by不兼容

SELECT *, `studentid`, COUNT(studentid),
`be_user_profiles`.`first_name`, `be_user_profiles`.`last_name`
FROM `be_user_profiles` 
JOIN `be_users` ON `be_users`.`id`=`be_user_profiles`.`user_id` 
JOIN `hw_homework` ON `be_user_profiles`.`user_id`=`hw_homework`.`studentid` 
WHERE `be_user_profiles`.`advisor` = '20' 
AND `hw_homework`.`date` < '2018-06-15 00:00:00' 
AND `hw_homework`.`date` > '2017-08-24 00:00:00'
AND `active` = 1 
GROUP BY `be_user_profiles`.`user_id` 
ORDER BY COUNT(studentid) DESC

$this->db->select('*,studentid,COUNT(studentid),be_user_profiles.first_name,be_user_profiles.last_name');
$this->db->from('be_user_profiles');
$this->db->join('be_users','be_users.id=be_user_profiles.user_id');
$this->db->join('hw_homework','be_user_profiles.user_id=hw_homework.studentid');
$this->db->where('be_user_profiles.advisor',$id);
$this->db->where('hw_homework.date <',$to);
$this->db->where('hw_homework.date >',$from);
$this->db->where('active',1);
$this->db->group_by('be_user_profiles.user_id');
$this->db->order_by('COUNT(studentid)','DESC');
$query = $this->db->get();

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Answer 1

在标准SQL中，很难在具有groupby的查询中使用select*。为什么？标准SQL要求选定的列也出现在GROUP BY子句中，只有少数列的值在功能上依赖于GROUP BY中提到的值。

要编写聚合查询，最简单的方法是在SELECT中枚举所需的列，然后在GROUP中枚举所需的列。MySQL对group by的臭名昭著的非标准扩展允许您请求group by子句中没有提到的列，并继续为这些列返回一些不可预测的值。

要修复这个“权利”，而不是攻击它，您需要删除*。改变

 $this->db->select('*,studentid,COUNT(studentid),be_user_profiles.first_name,be_user_profiles.last_name');

$this->db->select('studentid,COUNT(studentid),be_user_profiles.first_name,be_user_profiles.last_name');

$this->db->group_by('be_user_profiles.user_id');

至

$this->db->group_by('studentid,be_user_profiles.user_id');