文档章节

Oracle和MySQL分组查询GROUP BY

小麦苗
 小麦苗
发布于 2017/07/29 12:19
字数 2050
阅读 21
收藏 0

Oracle和MySQL分组查询GROUP BY


真题1、Oracle和MySQL中的分组(GROUP BY)有什么区别?
答案:Oracle对于GROUP BY是严格的,所有要SELECT出来的字段必须在GROUP BY后边出现,否则会报错:“ORA-00979: not a GROUP BY expression”。而MySQL则不同,如果SELECT出来的字段在GROUP BY后面没有出现,那么会随机取出一个值,而这样查询出来的数据不准确,语义也不明确。所以,作者建议在写SQL语句的时候,应该给数据库一个非常明确的指令,而不是让数据库去猜测,这也是写SQL语句的一个非常良好的习惯。
下面给出一个示例。有一张T_MAX_LHR表,数据如下图所示,有3个字段ARTICLE、AUTHOR和PRICE。请选出每个AUTHOR的PRICE最高的记录(要包含所有字段)。

ARTICLE

AUTHOR

PRICE

0001

B

3.99

0002

A

10.99

0003

C

1.69

0004

B

19.95

0005

A

6.96

首先给出建表语句:
CREATE TABLE T_MAX_LHR (ARTICLE VARCHAR2(30),AUTHOR VARCHAR2(30),PRICE NUMBER); --Oracle
--CREATE TABLE T_MAX_LHR (ARTICLE VARCHAR(30),AUTHOR VARCHAR(30),PRICE FLOAT); --MySQL oracle通用
INSERT INTO T_MAX_LHR VALUES ('0001','B',3.99);
INSERT INTO T_MAX_LHR VALUES ('0002','A',10.99);
INSERT INTO T_MAX_LHR VALUES ('0003','C',1.69);
INSERT INTO T_MAX_LHR VALUES ('0004','B',19.95);
INSERT INTO T_MAX_LHR VALUES ('0005','A',6.96);
COMMIT;
SELECT * FROM T_MAX_LHR;
在Oracle中的数据:
LHR@orclasm > SELECT * FROM T_MAX_LHR;
ARTICLE  AUTHOR        PRICE
-------- -------- ----------
0001     B              3.99
0002     A             10.99
0003     C              1.69
0004     B             19.95
0005     A              6.96
在MySQL中的数据:
mysql> SELECT * FROM T_MAX_LHR;
+---------+--------+-------+
| ARTICLE | AUTHOR | PRICE |
+---------+--------+-------+
| 0001    | B      |  3.99 |
| 0002    | A      | 10.99 |
| 0003    | C      |  1.69 |
| 0004    | B      | 19.95 |
| 0005    | A      |  6.96 |
+---------+--------+-------+
5 rows in set (0.00 sec)
分析数据后,正确答案应该是:

ARTICLE

AUTHOR

PRICE

0002

A

10.99

0003

C

1.69

0004

B

19.95

对于这个例子,很容易想到的SQL语句如下所示:
SELECT T.ARTICLE,T.AUTHOR,MAX(T.PRICE) FROM T_MAX_LHR T GROUP BY T.AUTHOR; 
SELECT * FROM T_MAX_LHR T GROUP BY T.AUTHOR;
在Oracle中执行上面的SQL语句报错:
LHR@orclasm > SELECT T.ARTICLE,T.AUTHOR,MAX(T.PRICE) FROM T_MAX_LHR T GROUP BY T.AUTHOR; 
SELECT T.ARTICLE,T.AUTHOR,MAX(T.PRICE) FROM T_MAX_LHR T GROUP BY T.AUTHOR
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression




LHR@orclasm > SELECT * FROM T_MAX_LHR T GROUP BY T.AUTHOR;
SELECT * FROM T_MAX_LHR T GROUP BY T.AUTHOR
       *
ERROR at line 1:
ORA-00979: not a GROUP BY expression
在MySQL中执行同样的SQL语句不会报错:
mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.21-enterprise-commercial-advanced-log |
+-------------------------------------------+


mysql> SELECT T.ARTICLE,T.AUTHOR,MAX(T.PRICE) FROM T_MAX_LHR T GROUP BY T.AUTHOR;
+---------+--------+--------------+
| ARTICLE | AUTHOR | MAX(T.PRICE) |
+---------+--------+--------------+
| 0002    | A      |        10.99 |
| 0001    | B      |        19.95 |
| 0003    | C      |         1.69 |
+---------+--------+--------------+
3 rows in set (0.00 sec)


mysql> SELECT * FROM T_MAX_LHR T GROUP BY T.AUTHOR;
+---------+--------+-------+
| ARTICLE | AUTHOR | PRICE |
+---------+--------+-------+
| 0002    | A      | 10.99 |
| 0001    | B      |  3.99 |
| 0003    | C      |  1.69 |
+---------+--------+-------+
3 rows in set (0.00 sec)
虽然执行不报错,可以查询出数据,但是从结果来看数据并不是最终想要的结果,甚至数据是错乱的。下面给出几种正确的写法(在Oracle和MySQL中均可执行):
(1)使用相关子查询
SELECT *
  FROM T_MAX_LHR T
 WHERE (T.AUTHOR, T.PRICE) IN (SELECT NT.AUTHOR, MAX(NT.PRICE) PRICE
                                 FROM T_MAX_LHR NT
                                GROUP BY NT.AUTHOR)
 ORDER BY T.ARTICLE;


SELECT *
  FROM T_MAX_LHR T
 WHERE T.PRICE = (SELECT MAX(NT.PRICE) PRICE
                    FROM T_MAX_LHR NT
                   WHERE T.AUTHOR = NT.AUTHOR)
 ORDER BY T.ARTICLE;


(2)使用非相关子查询
SELECT T.*
  FROM T_MAX_LHR T
  JOIN (SELECT NT.AUTHOR, MAX(NT.PRICE) PRICE
          FROM T_MAX_LHR NT
         GROUP BY NT.AUTHOR) T1
    ON T.AUTHOR = T1.AUTHOR
   AND T.PRICE = T1.PRICE
 ORDER BY T.ARTICLE;
(3)使用LEFT JOIN语句
SELECT T.*
  FROM T_MAX_LHR T
  LEFT OUTER JOIN T_MAX_LHR T1
    ON T.AUTHOR = T1.AUTHOR
   AND T.PRICE < T1.PRICE
 WHERE T1.ARTICLE IS NULL
 ORDER BY T.ARTICLE;
在Oracle中的执行结果:
LHR@orclasm > SELECT T.*
  2    FROM T_MAX_LHR T
  3    LEFT OUTER JOIN T_MAX_LHR T1
  4      ON T.AUTHOR = T1.AUTHOR
  5     AND T.PRICE < T1.PRICE
  6   WHERE T1.ARTICLE IS NULL
  7   ORDER BY T.ARTICLE;


ARTICLE  AUTHOR        PRICE
-------- -------- ----------
0002     A             10.99
0003     C              1.69
0004     B             19.95
在MySQL中的执行结果:
mysql> SELECT T.*
    ->   FROM T_MAX_LHR T
    ->   LEFT OUTER JOIN T_MAX_LHR T1
    ->     ON T.AUTHOR = T1.AUTHOR
    ->    AND T.PRICE < T1.PRICE
    ->  WHERE T1.ARTICLE IS NULL
    ->  ORDER BY T.ARTICLE;
+---------+--------+-------+
| ARTICLE | AUTHOR | PRICE |
+---------+--------+-------+
| 0002    | A      | 10.99 |
| 0003    | C      |  1.69 |
| 0004    | B      | 19.95 |
+---------+--------+-------+
3 rows in set (0.00 sec)


真题2、Oracle和MySQL中的分组(GROUP BY)后的聚合函数分别是什么?
答案:在Oracle中,可以用WM_CONCAT函数或LISTAGG分析函数;在MySQL中可以使用GROUP_CONCAT函数。示例如下:
首先给出建表语句:
CREATE TABLE T_MAX_LHR (ARTICLE VARCHAR2(30),AUTHOR VARCHAR2(30),PRICE NUMBER); --Oracle
--CREATE TABLE T_MAX_LHR (ARTICLE VARCHAR(30),AUTHOR VARCHAR(30),PRICE FLOAT); --MySQL oracle通用
INSERT INTO T_MAX_LHR VALUES ('0001','B',3.99);
INSERT INTO T_MAX_LHR VALUES ('0002','A',10.99);
INSERT INTO T_MAX_LHR VALUES ('0003','C',1.69);
INSERT INTO T_MAX_LHR VALUES ('0004','B',19.95);
INSERT INTO T_MAX_LHR VALUES ('0005','A',6.96);
COMMIT;
SELECT * FROM T_MAX_LHR;
在MySQL中:
mysql> SELECT T.AUTHOR, GROUP_CONCAT(T.ARTICLE), GROUP_CONCAT(T.PRICE)
    ->   FROM T_MAX_LHR T
    ->  GROUP BY T.AUTHOR; 
+--------+-------------------------+-----------------------+
| AUTHOR | GROUP_CONCAT(T.ARTICLE) | GROUP_CONCAT(T.PRICE) |
+--------+-------------------------+-----------------------+
| A      | 0002,0005               | 10.99,6.96            |
| B      | 0001,0004               | 3.99,19.95            |
| C      | 0003                    | 1.69                  |
+--------+-------------------------+-----------------------+
3 rows in set (0.00 sec)
在Oracle中:
LHR@orclasm >  SELECT T.AUTHOR, WM_CONCAT(T.ARTICLE) ARTICLE, WM_CONCAT(T.PRICE)  PRICE
  2    FROM T_MAX_LHR T
  3   GROUP BY T.AUTHOR;


AUTHOR   ARTICLE         PRICE
-------- --------------- ---------------
A        0002,0005       10.99,6.96
B        0001,0004       3.99,19.95
C        0003            1.69


LHR@orclasm >  SELECT T.AUTHOR,
  2          LISTAGG(T.ARTICLE, ',') WITHIN GROUP(ORDER BY T.PRICE) ARTICLE,
  3          LISTAGG(T.PRICE, ',') WITHIN GROUP(ORDER BY T.PRICE) PRICE
  4     FROM T_MAX_LHR T
  5    GROUP BY T.AUTHOR;


AUTHOR   ARTICLE         PRICE
-------- --------------- ---------------
A        0005,0002       6.96,10.99
B        0001,0004       3.99,19.95
C        0003            1.69






  原作者不知道是谁了,这个图不是小麦苗画的。


 

MySQL分组查询group by使用示例

 

(1) group by的含义:将查询结果按照1个或多个字段进行分组,字段值相同的为一组
(2) group by可用于单个字段分组,也可用于多个字段分组

select from employee;

+------+------+--------+------+------+-------------+

| num  | d_id | name   | age  | sex  | homeaddr    |

+------+------+--------+------+------+-------------+

|    1001 | 张三   |   26 | 男   | beijinghdq  |

|    1002 | 李四   |   24 | 女   | beijingcpq  |

|    1003 | 王五   |   25 | 男   | changshaylq |

|    1004 | Aric   |   15 | 男   | England     |

+------+------+--------+------+------+-------------+
select from employee group by d_id,sex;
select from employee group by sex;

+------+------+--------+------+------+------------+

| num  | d_id | name   | age  | sex  | homeaddr  

|+------+------+--------+------+------+------------+

|    1002 | 李四   |   24 | 女   | beijingcpq |

|    1001 | 张三   |   26 | 男   | beijinghdq |

+------+------+--------+------+------+------------+

 

根据sex字段来分组,sex字段的全部值只有两个('男'和'女'),所以分为了两组 当group by单独使用时,只显示出每组的第一条记录 所以group by单独使用时的实际意义不大

 

group by + group_concat()

 

(1) group_concat(字段名)可以作为一个输出字段来使用,
(2) 表示分组之后,根据分组结果,使用group_concat()来放置每一组的某字段的值的集合

select sex from employee group by sex;

+------+

| sex  |

+------+

| 女   |

| 男   |

+------+

 

select sex,group_concat(name) from employee group by sex;

+------+--------------------+

| sex  | group_concat(name) |

+------+--------------------+

| 女   | 李四               |

| 男   | 张三,王五,Aric     |

+------+--------------------+

 

select sex,group_concat(d_id) from employee group by sex;

+------+--------------------+

| sex  | group_concat(d_id) |

+------+--------------------+

| 女   1002               |

| 男   1001,1003,1004     |

+------+--------------------+

 

group by + 集合函数


(1) 通过group_concat()的启发,我们既然可以统计出每个分组的某字段的值的集合,那么我们也可以通过集合函数来对这个"值的集合"做一些操作

 

select sex,group_concat(age) from employee group by sex;

+------+-------------------+

| sex  | group_concat(age) |

+------+-------------------+

| 女   24                |

| 男   26,25,15          |

+------+-------------------+

 


分别统计性别为男/女的人年龄平均值

select sex,avg(age) from employee group by sex;

+------+----------+

| sex  avg(age) |

+------+----------+

| 女   |  24.0000 |

| 男   |  22.0000 |

+------+----------+
分别统计性别为男/女的人的个数

select sex,count(sex) from employee group by sex;

+------+------------+

| sex  count(sex) |

+------+------------+

| 女   |          |

| 男   |          |

+------+------------+

 

group by + having


(1) having 条件表达式:用来分组查询后指定一些条件来输出查询结果
(2) having作用和where一样,但having只能用于group by

select sex,count(sex) from employee group by sex having count(sex)>2;

+------+------------+

| sex  count(sex) |

+------+------------+

| 男   |          |

+------+------------+

 

group by + with rollup


(1) with rollup的作用是:在最后新增一行,来记录当前列里所有记录的总和

select sex,count(age) from employee group by sex with rollup;

+------+------------+

| sex  count(age) |

+------+------------+

| 女   |          |

| 男   |          |

NULL |          |

+------+------------+

select sex,group_concat(age) from employee group by sex with rollup;

+------+-------------------+

| sex  | group_concat(age) |

+------+-------------------+

| 女   24                |

| 男   26,25,15          |

NULL 24,26,25,15       |

+------+-------------------+

 

 

 

About Me

.............................................................................................................................................

● 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

● 本文在itpub(http://blog.itpub.net/26736162/abstract/1/)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址:http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群:230161599     微信群:私聊

● 联系我请加QQ好友(646634621),注明添加缘由

● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

.............................................................................................................................................

小麦苗的微店https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

小麦苗出版的数据库类丛书http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(xiaomaimiaolhr)及QQ群(DBA宝典),学习最实用的数据库技术。

   小麦苗的微信公众号          小麦苗的QQ群             小麦苗的微店

.............................................................................................................................................

 

DBA笔试面试讲解 欢迎与我联系

 

 

© 著作权归作者所有

共有 人打赏支持
小麦苗
粉丝 25
博文 51
码字总数 384095
作品 0
浦东
数据库管理员
.NET 数据库访问框架 Chloe.ORM v2.18 丰富子查询

Chloe.ORM:一款高质量的.Net C#数据库访问框架(ORM)。支持SqlServer、MySql、Oracle及SQLite,实体为纯POCO,支持基本数据类映射的同时也支持枚举类型。查询接口借鉴linq,支持lambda表达式...

so_while
2017/09/26
619
2
mysql基础知识理解和sql题讲解分析面试实战(一)之groupBy&&having

mysql 之groupBy&&having 的使用 groupby和having在什么情况下使用 使用时需要注意什么 groupby和having在什么情况下使用 问题 :根据平均值分析表 demo :原始表 结果表:根据每位user的rate的...

布道牛
2016/03/30
63
0
sql中筛选第一条记录

问题描述 我们现在有一张表titles,共有4个字段,分别是empno(员工编号),title(职位),fromdate(起始时间),todate(结束时间),记录的是员工在某个时间段内职位名称,因为会存在升职,转岗之...

Awesome_Tang
01/21
0
0
Oracle的sql (一 )

Oracle体系结构: 数据库 ----> 实例(orcl) ---> 表空间(逻辑单位)(用户) ---> 数据文件(物理单位) 地球 ----> 国家 ---> 省份(省长,公民) ---> 中粮,山川河流 Oracle和mysql区别: 收费, 不开...

勤劳的开发者px
2017/10/23
0
0
CentOS 7安装MySQL

安装mysql前提:安装好CentOS 7 64位,CentOS 7系统可以连接网络 完成之后打开终端,检查mysql是否已安装: 在终端中输入 1 如下图: 这显示已经安装了mysql,如果已安装,那么先卸载,在终端...

寰宇01
05/11
0
0

没有更多内容

加载失败,请刷新页面

加载更多

6. Python3源码—List对象

6.1. List对象 List对象是“变长对象”。 6.1.1. Python中的创建 Python中List对象最重要的创建方法为PyList_New,如下Python语句最终会调用到PyList_New: test = [1, 2, 3, 4, 5] 6.1.2. ...

Mr_zebra
9分钟前
1
0
nginx屏蔽指定接口(URL)

Step1:需求 web平台上线后,需要屏蔽某个服务接口,但又不想重新上线,可以采用nginx屏蔽指定平台接口的办法 Step2:具体操作 location /dist/views/landing/UNIQUE_BEACON_URL { re...

Linux_Anna
16分钟前
2
0
tomcat高并发配置调优

作者:Joker-pan 原文:https://blog.csdn.net/u011622226/article/details/72510385?utm_source=copy --------------------- tomcat 解压就使用的,配置都没动过,肯定不能支持高并发了; ...

imbiao
35分钟前
2
0
mysql 联结,级联查询总结区分

其实我对 数据库的级联或者联结查询一直都是会用,项目能查询出来自己想要的结果即可。 毕竟SQL使用复杂的查询毕竟比较少,而且不难使用。 至于区分他们,我还真的有点模糊。 在看 《SQL必知...

之渊
52分钟前
2
0
区块链入门教程分享区块链POW证明代码实现demo

兄弟连区块链入门教程分享区块链POW证明代码实现demo 这里强调一下区块链的协议分层 应用层 合约层 激励机制 共识层 网络层 数据层 上 一篇主要实现了区块链的 数据层,数据层主...

兄弟连区块链入门教程
59分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部