浅谈mysql数据库和oracle数据库之group by 之mysql5.6与5.7group by

众所周知，group by 子句是用来分组的，by后面跟上字段，表示以哪个字段之后，那么oracle和mysql的group by有何区别呢？接下来咱们就一起来探讨一下。

共同点

写法：select 字段1，字段2......

from 表

where 条件

group by 字段

having 条件；

注意：1.group by 必须配合聚合函数使用，聚合函数一般跟在select后

2.如果分组后还要进行筛选，也就是说group by 后还要加条件的话，切记不能用where，要使用having

技巧：1.遇到统计想函数

2.形容词前group by

3.函数中央是名词

4.列名select后添加

声明：技巧来自老男孩oldguo大神

不同点

oracle：所有出现在select后面的字段名（函数用的字段不用管），必须出现在group by语句后，没有在select后面出现的字段名，是不允许在group by后面的。

举例：求同一部门同一岗位有几个人（scott下的emp表）

select deptno ,job ,count(*)

from emp

group by deptno,job ；

这里是按照2个字段的组合分成一组，另外各位看到deptno和job分别出现在select后面和group by 后面这样才是正确的

select deptno ,job ,count(*)

from emp

group by deptno ；

如果group by 之后只跟deptno 就错了

mysql：select后面出现的字段，除函数用的字段外，无需全部出现在group by 之后。

举例：统计中国各个省的平均人口数，显示结果是大于平均人口数的城市信息（world库里的city表）

SELECTcity.District,city.`Name`,city.`Population`,AVG(city.Population)

FROM city

WHERE city.countrycode='chn'

GROUP BY city.District

HAVING city.Population > AVG(city.Population);

这条语句group by后面只跟了city.district一个字段，但是select后面除了函数所在字段外还有city.District,city.`Name`,city.`Population`,，共三个字段啊，如果是在oralce数据库上，绝对会报错，但是大家注意，我这里说的是mysql,而且是mysql5.6版本，所以，请大家不要怀疑，这条SQL绝对能执行成功，但是必须得是在mysql5.6上才能执行成功。也就是说如果你使用的是mysql5.7版本的话，就需要注意了，默认情况下，我说的是默认，这条语句是绝对执行不成功的。为什么呢?因为5.6和5.7版本不同（废话，哈哈）

如果在5.7上要顺利执行以上语句，有两种办法

第一种:加函数，把group by后面没有但是select后面有的字段（函数所在字段除外）用any_value括起来。比如上述语句中，group by后面没有但是select后面有的字段（函数所在字段除外）是city.`Name`,city.`Population`，那么就得把这两个字段括起来，如：any_value(city.`Population`) ，any_value(city.name) ，正确的写法如下：

SELECT city.`District`,any_value(city.`Population`) ap ,any_value(city.name) an , AVG(Population) avgp

FROM city

WHERE city.`CountryCode`='chn'

GROUP BY city.`District`

HAVING ap > avgp;

为什么要加别名呢？这就是另外一个需要注意的地方了，使用having时，如果说需要原表数据和统计后的值进行比较时，需要在调用列的时候加全表名或者别名。怎么样，记住了吗？

第二种：修改sql_mode参数

SELECTcity.District,city.`Name`,city.`Population`,AVG(city.Population)

FROM city

WHERE city.countrycode='chn'

GROUP BY city.District

HAVING city.Population > AVG(city.Population);

这条语句直接在5.7上使用的话绝对会报错，罪恶的源头就是sql_mode=only_full_group_by这个参数，只需把这个参数关掉即可。

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUPBY clause and contains nonaggregated column 'world.city.Name' which is notfunctionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

做法：

第一步：查看参数

select @@sql_mode;

+-------------------------------------------------------------------------------------------------------------------------------------------+

| @@sql_mode |

+-------------------------------------------------------------------------------------------------------------------------------------------+

|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION|

+-------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

第二步：修改参数注意：只是临时修改，相当于会话变量，只在当前会话中生效。

setsql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Query OK, 0 rows affected (0.00 sec)

第三步：执行SQL

mysql> SELECTcity.District,city.`Name`,city.`Population`,AVG(city.Population)

-> FROM city

-> WHERE city.countrycode='chn'

-> GROUP BY city.District

-> HAVING city.Population > AVG(city.Population);

+----------------+---------------------+------------+----------------------+

+----------------+---------------------+------------+----------------------+

| Anhui | Hefei | 1369100 | 321321.0000 |

| Fujian | Fuzhou | 1593800 | 297970.8333 |

| Gansu | Lanzhou | 1565800 | 351804.4286 |

| Guangdong | Kanton [Guangzhou] | 4256300 | 475513.1500 |

| Guangxi | Nanning | 1161800 | 325015.7778 |

| Guizhou | Guiyang | 1465200 | 418681.1667 |

| Hainan | Haikou | 454300 | 278560.0000 |

| Hebei | Shijiazhuang | 2041500 | 538212.7500 |

| Heilongjiang | Harbin | 4289800 | 553717.0000 |

| Henan | Zhengzhou | 2107200 | 383278.3333 |

| Hubei | Wuhan | 4344600 | 388526.5909 |

| Hunan | Changsha | 1809800 | 302181.9444 |

| Inner Mongolia | Baotou | 980000 | 317036.8462|

| Jiangsu | Nanking [Nanjing] | 2870300 | 388794.4000 |

| Jiangxi | Nanchang | 1691600 | 348323.4545 |

| Jilin | Changchun | 2812000 | 391341.2000 |

| Liaoning | Shenyang | 4265200 | 718055.9048 |

| Ningxia | Yinchuan | 544500 | 401181.0000 |

| Peking | Peking | 7472000 | 3784584.0000 |

| Shaanxi | Xi´an | 2761400 | 537186.6250 |

| Shandong | Qingdao | 2596000 | 378575.5000 |

| Shanxi | Taiyuan | 1968400 | 463322.1111 |

| Sichuan | Chengdu | 3361500 | 355088.9048 |

| Xinxiang | Urumtši [Ürümqi] | 1310100 | 289470.5000 |

| Yunnan | Kunming | 1829500 | 490203.2000 |

| Zhejiang | Hangzhou | 2190500 | 362961.5000 |

+----------------+---------------------+------------+----------------------+

26 rows in set (0.00 sec)

完美！！！

总结：1.oracle：所有出现在select后面的字段名（函数用的字段不用管），必须出现在group by语句后，没有在select后面出现的字段名，是不允许在group by后面的。

2.使用having时，如果说需要原表数据和统计后的值进行比较时，需要在调用列的时候加全表名或者别名。

3.在select后的查询里，必须是在group by出现过的列，或者使用函数（any_value）将group by 后面没有的列括起来的，否则会报错。解决办法一：加函数。二：改参数。

以上观点仅代表一家之言，如有不足之处，欢迎大家批评指正。QQ：670340797 wechat:lhl9223

浅谈mysql数据库和oracle数据库之group by 之mysql5.6与5.7group by

推荐阅读更多精彩内容