MySQL和mariadb的GROUP BY有几个扩展特性(都是标准SQL不支持的):(1).能够在group by中使用列别名;(2).可以在select_list中使用非分组列;(3).可以在group by子句中指定分组列的升序和降序排序。下面分别说明这些特性。
(1).group by中能够使用列别名。
其实对于MySQL和mariadb而言,并非是有一个专门的select_list筛选过程,使得筛选完成后,后续的步骤就能使用这些筛选出来的列。而是从WHERE子句筛选了行之后,后面所有的过程都可以对select_list进行检索扫描。其中ORDER BY子句扫描select_list的时候是先检索出列表达式,再检索所引用表中的列,直到找出所有的排序列;而GROUP BY和HAVING子句则是先检索表中的列,再检索列表达式,直到找出所有的分组列。因此,MySQL、mariadb能够使用列别名。
下面两个查询的例子很能说明问题:
MariaDB [test]> set @a:=0;select sid,name,class,@a:=@a+1 as class from Student order by class;
+------+----------+--------+-------+
| sid | name | class | class |
+------+----------+--------+-------+
| 1 | chenyi | Java | 1 |
| 2 | huanger | Python | 2 |
| 3 | zhangsan | Java | 3 |
| 4 | lisi | C# | 4 |
| 5 | wangwu | Python | 5 |
| 6 | zhaoliu | Java | 6 |
| 7 | qianqi | C | 7 |
| 8 | sunba | C++ | 8 |
| 9 | yangjiu | Java | 9 |
+------+----------+--------+-------+
MariaDB [test]> set @a:=0;select sid,name,class,@a:=@a+1 as class from Student group by class;
+------+---------+--------+-------+
| sid | name | class | class |
+------+---------+--------+-------+
| 7 | qianqi | C | 7 |
| 4 | lisi | C# | 4 |
| 8 | sunba | C++ | 8 |
| 1 | chenyi | Java | 1 |
| 2 | huanger | Python | 2 |
+------+---------+--------+-------+
上面两个查询中,表达式 @a:=@a+1 的别名为class,和Student表中的class列重复。在第一个查询中,使用order by对class排序,由于order by先从select_list中的列表达式开始检索,因此这个排序列class是 @a:=@a+1 对应的列,结果也正符合此处的分析。第二个查询中,使用group by对class进行分组,因为它先检索表的字段名,因此这个分组列class是Student中的class列,结果也同样符合此处的分析。
但是,在标准SQL中这是不允许的行为。虽然在select_list中出现两个同名的列名称是允许的,但是在引用列别名的时候,无论是group by还是order by子句或其他子句,都认为同列名会导致二义性。标准SQL严格遵循select_list是"同时性的",引用列的时候无法像mysql/mariadb一样分先后顺序地检索select_list。
(2).在group by子句中可以指定分组列的升序和降序排序。
无论是标准SQL还是MySQL、mariadb,group by分组的时候,都会按照分组列升序排序。只不过标准SQL中只能使用默认的升序,而MySQL、mariadb可以自行指定排序方式。
例如:
MariaDB [test]> select class from Student group by class;
+--------+
| class |
+--------+
| C |
| C# |
| C++ |
| Java |
| Python |
+--------+
很明显,结果中是按照分组列class进行升序排序的。
在MySQL、mariadb中可以为group by子句指定排序方式。而MS SQL和Oracle不允许。
MariaDB [test]> select class from Student group by class desc;
+--------+
| class |
+--------+
| Python |
| Java |
| C++ |
| C# |
| C |
+--------+
不过MS SQL和Oracle也能实现同样的功能,只需使用ORDER BY即可。
请记住,GROUP BY子句默认会进行排序,这一点很重要。
(3).在select_list中可以使用非分组列。
MySQL和MariaDB在这里又"偷奸耍滑"了。
如下查询: