mysql group by 问题

淩亂°似流年 2023-02-17 02:57 14阅读 0赞

## mysql group by 问题 ##

sql：select t1.id,t1.deptName,t1.locAdd,count(t2.name)as emps from tbl\_dept t1 left join tbl\_emp t2 on t2.deptId=t1.id group by deptName order by id;  
报错信息：  
ERROR 1055 (42000): Expression \#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.t1.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql\_mode=only\_full\_group\_by

![在这里插入图片描述][20200611163125356.png]  
查看解释是：  
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，也就是说查出来的列必须在group by后面出现，否则就会报错，或者这个字段出现在聚合函数里面。

select @@GLOBAL.sql\_mode;  
![在这里插入图片描述][20200611163253609.png]  
select @@SESSION.sql\_mode;  
![在这里插入图片描述][20200611163301691.png]  
执行：  
set sql\_mode=‘STRICT\_TRANS\_TABLES,NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_ENGINE\_SUBSTITUTION’;  
![在这里插入图片描述][20200611163335586.png]  
查看select @@SESSION.sql\_mode;  
![在这里插入图片描述][202006111633574.png]

执行最上面的sql语句,即可正常显示  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcxMzQ5OA_size_16_color_FFFFFF_t_70]  
感谢大佬分享：  
https://blog.csdn.net/qq\_41895747/article/details/106155963?%3E?utm\_medium=distribute.pc\_relevant.none-task-blog-baidujs-9

https://blog.csdn.net/free377096858/article/details/91963406

[20200611163125356.png]: https://img-blog.csdnimg.cn/20200611163125356.png
[20200611163253609.png]: https://img-blog.csdnimg.cn/20200611163253609.png
[20200611163301691.png]: https://img-blog.csdnimg.cn/20200611163301691.png
[20200611163335586.png]: https://img-blog.csdnimg.cn/20200611163335586.png
[202006111633574.png]: https://img-blog.csdnimg.cn/202006111633574.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcxMzQ5OA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200611163417986.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcxMzQ5OA==,size_16,color_FFFFFF,t_70