MySQL和Oracle的groupby的异同
⼀直弄混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)
---------------
##写在最后
&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时可以省略
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)groupby分组
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]…]];
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论