MySQL和Oracle的group by的异同

  一直弄混MySQL和Oracle之间使用group by时的用法,搞得后来都不会用group by了= =,今天正好用到,就干脆重新研究了一下。


1.Oracle里的group by查询语句

SELECT column, group_function,... FROM table
[WHERE condition]
GROUP BY group_by_expression
[HAVING group_condition];

2.MySQL里的group by查询语句

SELECT [field1, field2,...,fieldn] | fun_name FROM table
[WHERE condition]
GROUP BY [field1, field2,...,fieldn]
[WITH ROLLUP]
[HAVING group_condition];

  两者之间最明显的区别在于:Oracle里,SELECT子句后面的所有目标列或目标表达式要么是分组列,要么是分组表达式,要么是聚集函数,即Oracle分组查询必须查询分组字段或分组字段构成的表达式或聚集函数;而MySQL里SELECT后面的字段并没有这样的限制,MySQL里SELECT后面可以是表里的任何字段或这些字段的表达式。不过,Oracle里使用聚集函数的时候,聚集函数的参数可以是该表里的任意合法字段

  由于MySQL分组查询的时候允许查询非分组字段,所以当我们执行select * from …group by…时,每个分组只显示该分组的第一条记录,比如下面的查询,记录2和记录4没有显示,如果加where条件将第一条记录筛选出去了,那么同样的主sql语句就会出现第二条记录。

+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 |  100.00 |      1 |
| bjguan | 2004-04-02 |  100.00 |      1 |
| lisa   | 2003-02-01 |  200.00 |      2 |
| lili   | 2012-02-12 |  150.00 |      2 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
| dony   | 2005-02-05 | 2000.00 |      4 |
+--------+------------+---------+--------+
mysql> select * from emp group by deptno;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 |  100.00 |      1 |
| lisa   | 2003-02-01 |  200.00 |      2 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
| dony   | 2005-02-05 | 2000.00 |      4 |
+--------+------------+---------+--------+
4 rows in set (0.00 sec)
mysql> select * from emp where ename<>'zzx' group by deptno;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| bjguan | 2004-04-02 |  100.00 |      1 |
| lisa   | 2003-02-01 |  200.00 |      2 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
| dony   | 2005-02-05 | 2000.00 |      4 |
+--------+------------+---------+--------+
4 rows in set (0.00 sec)

  聚合函数:聚合函数用于对分组进行统计。如果未对查询分组,则聚集函数将作用于整个查询结果;如果对分组了,则聚集函数对每个分组分别进行统计。MySQL和Oracle一样。

+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx    | 2000-01-01 |  100.00 |      1 |
| bjguan | 2004-04-02 |  100.00 |      1 |
| lisa   | 2003-02-01 |  200.00 |      2 |
| lili   | 2012-02-12 |  150.00 |      2 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
| dony   | 2005-02-05 | 2000.00 |      4 |
+--------+------------+---------+--------+
mysql> select deptno,max(sal) from emp group by deptno;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
|      1 |   100.00 |
|      2 |   200.00 |
|      3 |  4000.00 |
|      4 |  2000.00 |
+--------+----------+
4 rows in set (0.00 sec)

  WHERE和HAVING的区别:where是在聚合前对记录进行筛选,而having是在分组结束后的结果里筛选,最后返回整个sql的查询结果。可以把having理解为两级查询,即含having的查询操作为先获得不含having子句时的sql查询结果表,然后在这个结果表上使用having条件筛选出符合的记录,最后返回这些记录,因此,having后是可以跟聚合函数的,并且这个聚集函数不必与select后面的聚集函数相同,而having后的字段限制和前面讲得一样,MySQL可以为表里的任何字段,而Oracle只能为分组字段,其实,只要有group by,那么Oracle里除where子句的其他任何子句里出现的字段都必须是分组字段(不过,该子句里出现的子查询不受这个限制)


这里写图片描述
emp表

//Oracle环境
SQL> select deptno,max(sal) from emp group by deptno;

    DEPTNO   MAX(SAL)
---------- ----------
        30       2850
        20       3000
        10       5000
SQL> select deptno,max(sal) from emp group by deptno having avg(deptno)<>30;

    DEPTNO   MAX(SAL)
---------- ----------
        20       3000
        10       5000

  WITH ROLLUP:注意到MySQL里有一个Oracle没有子句是with rollup,这个子句的作用是对分组聚合后的结果进行一次汇总,即把聚合函数的结果求和,非聚合函数列则置为NULL。

mysql> select deptno,sum(sal) from emp group by deptno with rollup;
+--------+----------+
| deptno | sum(sal) |
+--------+----------+
|      1 |   200.00 |
|      2 |   350.00 |
|      3 |  4000.00 |
|      4 |  2000.00 |
|   NULL |  6550.00 |
+--------+----------+
5 rows in set (0.00 sec)
mysql> select deptno,count(sal) from emp group by deptno with rollup;
+--------+------------+
| deptno | count(sal) |
+--------+------------+
|      1 |          2 |
|      2 |          2 |
|      3 |          1 |
|      4 |          1 |
|   NULL |          6 |
+--------+------------+
5 rows in set (0.00 sec)

写在最后

  不管是MySQL还是Oracle,select查询语句的写法是有严格的顺序关系的,比如说排序子句必须在where、分组几句后面。

1.完整的MySQL查询语句

select [distinct] {*|field1,field2,...|expression...} from table_name[, view_name]
[where condition]
[group by field [having group_condition]]
[order by field [ASC|DESC]]
[limit 偏移量,记录数];//偏移量为0时可以省略

  limit语句为MySQL独有的,偏移量表示相对于不含limit子句查询结果的第一条记录的起始偏移量,默认情况下为0,此时可省略,记录数为查询结果的最大记录条目数(有可能没有这么多条结果),同时limit子句还可以写成“limit 记录数 offset 偏移量”这种形式。

mysql> select * from emp order by deptno limit 2;
+--------+------------+--------+--------+
| ename  | hiredate   | sal    | deptno |
+--------+------------+--------+--------+
| zzx    | 2000-01-01 | 100.00 |      1 |
| bjguan | 2004-04-02 | 100.00 |      1 |
+--------+------------+--------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp order by deptno limit 3, 2;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| lili   | 2012-02-12 |  150.00 |      2 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
+--------+------------+---------+--------+
2 rows in set (0.00 sec)
mysql> select * from emp order by deptno limit 2 offset 3;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| lili   | 2012-02-12 |  150.00 |      2 |
| bzshen | 2005-04-01 | 4000.00 |      3 |
+--------+------------+---------+--------+
2 rows in set (0.00 sec)

2.完整的Oracle查询语句

select [all|distinct] column_name[, expression...] from table_name[, view_name, ...]
[where condition]
[group by column_name[, group_function] [having group_condition]]
[order by column_name [ASC|DESC][,column_name [ASC|DESC]...]];
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/rr123rrr/article/details/77950013
文章标签: oracle mysql
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭