一直弄混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];
1
2
3
4
2.MySQL里的group by查询语句
SELECT [field1, field2,...,fieldn] | fun_name FROM table
[WHERE condition]
GROUP BY [field1, field2,...,fieldn]
[WITH ROLLUP]
[HAVING group_condition];
1
2
3
4
5
两者之间最明显的区别在于: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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
聚合函数:聚合函数用于对分组进行统计。如果未对查询分组,则聚集函数将作用于整个查询结果;如果对分组了,则聚集函数对每个分组分别进行统计。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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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)
1
30 2850
20 3000
10 5000
1
2
3
SQL> select deptno,max(sal) from emp group by deptno having avg(deptno)<>30;
DEPTNO MAX(SAL)
1
20 3000
10 5000
1
2
------
  WITH ROLLUP:注意到MySQL里有一个Oracle没有子句是with rollup,这个子句的作用是对分组聚合后的结果进行一次汇总,即把聚合函数的结果求和,非聚合函数列则置为NULL。
1
2
3
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查询语句
1
2
3
4
5
6
7
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 偏移量”这种形式。
1
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查询语句
1
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]…]];