文档章节

Oracle和MySQL分组查询GROUP BY

小麦苗
 小麦苗
发布于 2017/07/29 12:19
字数 2050
阅读 17
收藏 0
点赞 0
评论 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笔试面试讲解 欢迎与我联系

 

 

© 著作权归作者所有

共有 人打赏支持
小麦苗
粉丝 23
博文 45
码字总数 362979
作品 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
[coreseek/sphinx学习笔记4]--搜索

[参考Coreseek 全文检索服务器 2.0 (Sphinx 0.9.8)参考手册,详情见http://www.coreseek.cn/docs/sphinxdoczhcn_0.9.pdf ] 4.1 匹配模式 有如下可选的匹配模式: SPHMATCHALL, 匹配所有查询词...

酒肉穿肠过
2013/02/23
0
1
打造Orm经典.Moon.Orm开源及技术文档发布.(您还用hibernate?实体框架?)

一、Moon.Orm框架总述 (您还用hibernate?实体框架?) 1.框架名:Moon 意思是月亮,而非Mono.因为很喜欢明月,所以以此为名.它是一个.NET下的Orm框架. 2.发展历史:历经近乎三年的发展历程,起因是E...

铂金系列
2013/09/14
1K
11
[MySql]关于sql和mysql对于别名不能调用的一些理解

由于有部分语句别名不能调用原来是由于别名机制不同引起的,为了避免下次发生就整理了一下sql和mysql执行顺序发现内部机制是一样的,最大区别是在别名的引用上 在写sql的时候,由于有部分语句...

NateHuang
2016/11/17
25
0
优化:mysql查询最近一条记录

下策——查询出结果后将时间排序后取第一条 这样做虽然可以取出当前时间最近的一条记录,但是一次查询需要将表遍历一遍,对于百万以上数据查询将比较费时;limit是先取出全部结果,然后取第一...

一只阿木木
05/25
0
0
druid 中有MySqlSelectGroupByExpr类,这是mysql用到的,oracle用的group by是什么类

@wenshao ,温少,你好,我在用druid 1.0.12时 发现Mysql有相关的GroupBy操作的类,Oracle相关的为啥没有。是共用的么? 不是共用的话,Oracle相关的怎么操作GroupBy。...

liyghting
2016/12/22
169
2

没有更多内容

加载失败,请刷新页面

加载更多

下一页

IntelliJ IDEA 详细图解最常用的配置,适合刚刚用的新人。

刚刚使用IntelliJ IDEA 编辑器的时候,会有很多设置,会方便以后的开发,磨刀不误砍柴工。 比如:设置文件字体大小,代码自动完成提示,版本管理,本地代码历史,自动导入包,修改注释,修改...

kim_o
13分钟前
0
0
Google Java编程风格指南

目录 前言 源文件基础 源文件结构 格式 命名约定 编程实践 Javadoc 后记 前言 这份文档是Google Java编程风格规范的完整定义。当且仅当一个Java源文件符合此文档中的规则, 我们才认为它符合...

niithub
15分钟前
0
0
java.net.MalformedURLException异常说明

1.异常片段 Java代码中,在进行URL url = new URL(urllink)操作时,提示以下异常信息,该类异常主要问题出在参数urllink上面。 异常片段1 java.net.MalformedURLException at java.ne...

lqlm
15分钟前
0
0
CentOS7修改mysql5.6字符集

解决办法:CentOS7下修改MySQL数据库字符编码为UTF-8,UTF-8包含全世界所有国家所需要的字符集,是国际编码。 具体操作如下: 1.进入MySQL [root@tianqi-01 ~]# mysql -uroot -p Enter passw...

河图再现
17分钟前
0
0
DevExpress v18.1新版亮点——WPF篇(一)

用户界面套包DevExpress v18.1日前终于正式发布,本站将以连载的形式为大家介绍各版本新增内容。本文将介绍了DevExpress WPF v18.1 的新功能,快来下载试用新版本!点击下载>> Accordion Co...

Miss_Hello_World
20分钟前
0
0
Rancher 2.0集群与工作负载告警

Rancher 2.0操作指南。本文将step by step演示如何使用Rancher 2.0中集成的告警功能,包括设置通知程序、设置集群级别以及工作负载级别的告警。 在Rancher 1.x时期,告警功能是很多Rancher用...

RancherLabs
24分钟前
1
0
Python中字符串拼接的N中方法

python拼接字符串一般有以下几种方法: ①直接通过(+)操作符拼接 s = 'Hello'+' '+'World'+'!'print(s) 输出结果:Hello World! 使用这种方式进行字符串连接的操作效率低下,因为python中...

木头释然
26分钟前
9
0
JAVA排序之归并排序

归并排序思路就是不停拆分数组,直到拆成一个一个元素,之后将拆出来的元素按照拆分顺序反过来的顺序合并,出现前边值大于后边值,则换位置,放入临时数组,最后将临时数组覆盖原数组. public stat...

勤奋的蚂蚁
37分钟前
1
0
想要年薪20万,先看会不会这28个企业运维岗经典面试题

1、Linux如何挂载windows下的共享目录? mount.cifs //IP地址/server /mnt/server -o user=administrator,password=123456 linux 下的server需要自己手动建一个 后面的user与pass 是windows...

Py爱好
43分钟前
0
0
python的url分配

from django.conf.urls import url,include urlpatterns=[ url('xinwen/',include('xinwen.urls')) ] from django.urls import path from xinwen import views urlpatterns = [ path('login/......

南桥北木
45分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部