009、分组查询(group by)

 

分组查询(group by)

分组查询
分组查询主要涉及到两个子句，分别是：group by 和 having，having不可单独出现，需要和group by 一起使用。
 
# -------------------------9、分组查询 -------------------------
# 取得每个工作岗位的工资合计，要求显示岗位名称和工资合计。
select * from emp;
select job, sum(sal) from emp group by job;

# 按照工作岗位和部门编码分组，取得的工资合计
# 在 SQL 语句中若有 group by 语句，那么在 select 语句后面只能跟分组函数+参与分组的字段。
select deptno, job, sum(sal) from emp group by deptno, job;

# 取得每个岗位的平均工资大于 2000
# 有group by语句，后面不能有where
select job, avg(sal) from emp group by job having avg(sal)>2000;

 

select 语句总结
一个完整的 select 语句格式如下：
　　select 字段
　　from 表名
　　where …….
　　group by ……..
　　having …….(就是为了过滤分组后的数据而存在的—不可以单独的出现)
　　order by ……..
以上语句的执行顺序
　　1. 首先执行 where 语句过滤原始数据
　　2. 执行 group by 进行分组
　　3. 执行 having 对分组数据进行操作
　　4. 执行 select 选出数据
　　5. 执行 order by 排序
 
原则：能在 where 中过滤的数据，尽量在 where 中过滤，效率较高。having 的过滤是专门对分组之后的数据进行过滤 的。

 

 

知识扩展：

分组查询group by的用法及讲解

参考资料：https://baijiahao.baidu.com/s?id=1664040284917893293&wfr=spider&for=pc

group by是sql中比较强大的功能，是在对数据分组统计时必不可少的用法。但是，对于很多经验不足的同学，经常会写错。今天我们就以Oracle为例，来讲解下分组查询group by的用法。

 

一，group by 语法规范

首先我们准备一张Student表

CREATE TABLE STUDENT

( SNO VARCHAR2(10) not null,

SNAME VARCHAR2(20),

SAGE NUMBER(2),

SSEX VARCHAR2(5) )

往里面插入几条学生实体记录。再查看数据：

SELECT * FROM STUDENT;

 

我们使用group by将这些数据按照性别进行分组：

SELECT * FROM STUDENT GROUP BY SSEX;

不幸的是，执行失败了，提示：不是 GROUP BY 表达式！(我用navicat 连接mysql 执行  SELECT * FROM t_student GROUP BY sex;  成功啊！不过还是按照他的思路搬运过来先，或许Oracle不行呢。) 

 

原因是group by 分组查询，select子句后的字段必须来自group by后的分组字段。于是 我们执行SQL

SELECT SSEX FROM STUDENT GROUP BY SSEX;

 

这下成功地将数据分为了两组。我们接下来使用下聚合函数

SELECT SSEX,MAX(SAGE) FROM STUDENT GROUP BY SSEX;

注意这条sql语句，select子句中聚合函数使用了SAGE(年龄)这个字段，那会不会违背了前面所说的 “select子句后的字段必须来自group by后的分组字段”这个规律呢，我们来执行一下：

 

能正常执行，成功地按照了性别分组，并且查询出了性别对应年龄最大的学生。于是我们可以得出规律：select子句后的任一非聚合函数字段都应来源于group by 分组语句后，否则语法会编译不通过。

 

二，group by 用法的意义

我们都知道group by 是用来分组的，那么具体怎么分组，对应的语句又怎么写呢？我们一起来研究下。

示例：按照年龄将学生分组。

很多人会这样写：

SELECT * FROM STUDENT GROUP BY SSAGE

上面已经证实过了，这样子语法不通过。

SELECT SSAGE FROM STUDENT GROUP BY SSAGE

如果这样写的话，也只会单纯地将年龄分组。

 

正确的写法是这样的：

SELECT SNAME,SAGE FROM STUDENT GROUP BY SAGE,SNAME ORDER BY SAGE;

 

可以看出，group by 分组是按照group by后的字段组合来进行分组的。也就是说你group by后给了我几个字段，我就按照这几个字段组合成一条记录，若有重复的记录，就属于同一组，最后将所有的分组返回给你。

同时上诉sql的order by语句也要符合group by的语法，即order by后的字段必须来源于group by分组字段。所以我们可以把分出来的组理解为试图，所有的其他操作都基于这张视图。

 

三，分组查询的筛选where和having

示例：查询出性别为男的学生并按照年龄分组，并筛选出年龄大于21岁的

我们首先实现钱半部分：查询出性别为男的学生并按照年龄分组

SELECT SNAME,SAGE FROM STUDENT where SSEX='男' GROUP BY SNAME,SAGE

 

然后使用having语句筛选：

SELECT SNAME,SAGE FROM STUDENT where SSEX='男' GROUP BY SNAME,SAGE HAVING SAGE>'21'

查询结果：

 

可以看到，小于等于21岁的男同学都被过滤掉了。

我们这里可以进行总结下：

1. 当在一个SQL中同时使用where和group by和having子句时，其执行顺序为：where>group by>having。

2. 同时where子句作用于表或者视图，having子句作用于组，having子句必须作用在group by之后。

以上三点就是分组查询group by的用法及讲解。

 

 

 

 

posted @ 2021-10-11 17:22  空-山-新-雨  阅读(539)  评论(0)  编辑  收藏  举报