关于mysql中，group by的一个使用心得

今天遇到了一个报错随着这个报错的解决感觉自己对于MySQL的理解更进一步了1、问题描述多表查询中对使用全连接得到的新表做group by操作报错[42000][1055] Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘deE.dname’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by2、前置背景1.由于MySQL不支持FULL JOIN因此通过使用UNION将左右连接的SQL语句进行合并以实现全连接2.group by后跟GROUP BY 的列必须是主键/唯一键或者非聚合列必须在 group by 中出现。以一个老掉牙的sql语句进行举例比如说在下面这两个SQL语句中前三行是一样的都是一个内连接而差别只在group by后面的内容。#aselect dept.deptno,dept.dname,dept.loc,count(*)from dept,emp where dept.deptnoemp.deptno group by dept.deptno;#bselect dept.deptno,dept.dname,dept.loc,count(*)from dept,emp where dept.deptnoemp.deptno group by emp.empno;这两条语句都是可以顺利执行的。其原因在于无论是deptno还是empno都是原先表格的主键其后所返回的内容即dnameloc均依赖于此。虽然在生成的新表中empno不是主键了但由于连接两表的条件dept.deptnoemp.deptnoMySQL会自发进行一个推理过程对于这个推理过程我暂时还不知道该如何展示自行脑补吧emp.empno 是主键 → 能确定 emp.deptno→通过等值连接条件emp.deptno 等于 dept.deptno→dept.deptno 是主键 → 能确定 dept.dname 和 dept.loc。由此我们可以知道对于连接后表依旧可以只在group by后添加之前的主键来返回和其有依赖关系的值。在这样的思路下我展开了奇思妙想一时手贱用全连接来进行查询前后代码如下select deE.deptno,deE.dname,deE.loc,COUNT(empno)from(SELECT d.deptno deptNO,d.dname dname,d.loc loc,e.empno empno,e.ename ename,e.job job,e.mgr mgr,e.hiredate hiredate,e.sal sal,e.comm FROM dept d LEFT JOIN emp e ON d.deptnoe.deptno UNION SELECT d.deptno deptNO,d.dname dname,d.loc loc,e.empno empno,e.ename ename,e.job job,e.mgr mgr,e.hiredate hiredate,e.sal sal,e.comm FROM dept d RIGHT JOIN emp e ON d.deptnoe.deptno)as deE group by deE.deptno,deE.dname,deE.loc;select deE.deptno,deE.dname,deE.loc,COUNT(empno)from(SELECT d.deptno deptNO,d.dname dname,d.loc loc,e.empno empno,e.ename ename,e.job job,e.mgr mgr,e.hiredate hiredate,e.sal sal,e.comm FROM dept d LEFT JOIN emp e ON d.deptnoe.deptno UNION SELECT d.deptno deptNO,d.dname dname,d.loc loc,e.empno empno,e.ename ename,e.job job,e.mgr mgr,e.hiredate hiredate,e.sal sal,e.comm FROM dept d RIGHT JOIN emp e ON d.deptnoe.deptno)as deE group by deE.deptno;同上面的举例一样这里的前后两个语句只有最后的group by后跟着的内容是不一样的。第一个语句可以顺利执行第二个语句报错[42000][1055] Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘deE.dname’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_modeonly_full_group_by那么为什么都是连接表这里会无法执行呢此前我是知其然不知其所以然只知道不能这样写但不知道为什么不可以这样写。在翻了多篇文章后我大致能推测出导致这种报错的原因免责声明纯猜测。原因就是这里在对全连接得到的表进行查询的时候把其视为一张单表deE了而得到的这个新表是没有原表的那些约束的也就是说MySQL知道每个表的主键也知道emp.deptno dept.deptno 意味着什么并可以沿着连接条件传递功能依赖。但是MySQL 无法确定 派生表中的deptno 是否唯一。虽然知道 deptno 来自 dept 表的主键但经过 UNION 后这个信息丢失了无法进行推理下去了