MySQL和Oracle的group by的异同

最新推荐文章于 2024-05-06 02:16:20 发布

JeansPocket

最新推荐文章于 2024-05-06 02:16:20 发布

阅读量1w

点赞数 3

分类专栏：数据库文章标签： oracle mysql

本文链接：https://blog.csdn.net/rr123rrr/article/details/77950013

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一直弄混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子句的其他任何子句里出现的字段都必须是分组字段（不过，该子句里出现的子查询不受这个限制）。

![这里写图片描述](https://img-blog.csdn.net/20170912213037925?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvcnIxMjNycnI=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast) 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


------
&emsp;&emsp;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)


---------------

##写在最后
&emsp;&emsp;不管是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时可以省略

&emsp;&emsp;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]…]];