对比Excel学习SQL(5):分组和子查询
今天这篇文章是对比Excel学习SQL系列的第5篇文章,之前3篇的传送门在这里:《计算字段》、《对行和列的操作》、《库/表/记录的增查删改》、《写给小白的SQL学习文章》。
本节内容要用到的数据表:成绩表(sc),学生基本信息表(student1)
一、分组
01 创建分组
group by 命令用来创建分组,分组其实相当好理解,就是Excel里的数据透视表,汇总方式为计数、求和或平均的功能,所以通常和聚合函数结合起来使用。
--语法select 列,聚合函数 from 表 where子句 group by 列;
Group by 要放在where之后,order by 之前
Q1:统计每个学号(snum)下的人数
用到【group by + count】,对snum学号一列分组并计数,相当于Excel透视表中把snum拖到列标签,同时对snum计数,结果是一样的。
select snum, count(*) from sc group by snum;
同理,你可以试试对cnum和score分组计数是什么结果。
Q2:统计每个学号(snum)下的总成绩
【group by + sum】,结合sum函数,可以统计每个学号的总成绩,Excel里是对score求和。
select snum,sum(score) from sc group by snum;
Q3:统计每个学号和成绩号下的平均成绩
Excel透视表里这样做,把snum和cnum拖动到行标签,score计数项求平均。
【group by 对一个以上的列】,对一个以上的列分组group by后面的每个列之间要用逗号隔开。如下图所示的意思是,先对snum分组,再对cnum分组,分组后得到的值求平均值,结果和Excel透视表是一样的。
select snum,cnum,avg(score) from sc group by snum,cnum;
02 分组后过滤
使用group by创建了分组以后,如果要做保留哪些分组,排除哪些分组的操作,就要用到having命令了。
--语法select 列,聚合函数 from 表 where子句 group by 列 having 聚合函数 操作符 值;
某网站用户登陆浏览记录表如下,一条行记录表示用户在哪个时间段登陆了一次,singin字段表示用户点击的次数,统计每个用户登陆网站的次数(复习一下刚刚的group by)
Q4:统计登陆网站次数大于等于2次的用户有哪些。
select name,count(*) from employee_tb1 group by name having count(*) >= 2;
相当于透视以后再做一次筛选
与where的区别
Where和having都是过滤的命令,但是因为where不能过滤聚合函数,所以要用到having,他们的不同之处是:
Where过滤的是行,having过滤的是分组;
Where用在分组前,having用在分组后
【与where同时使用】
Q5:统计点击次数大于等于2次的用户中登陆网站次数大于等于2次的用户。
select name,count(*) from employee_tb1 where singin>=2 group by name having count(*) >=2;
分组和排序
分组以后的顺序不一定是升序或降序排列,以下是用order by和不用的区别,可以很明显的看出来。分组一般搭配排序,order by写在group by的后面。
二、子查询
子查询就是在查询里再嵌套一个查询,子查询也很好理解,类似Excel里的IF函数嵌套的概念。
where中使用子查询就是在where语句后用操作符(>,<,=,in)连接另一个查询。
--语法select 列 from 表 where 操作符 (select 列 from 表)
Q6:找出成绩大于学号为7的学生成绩的学生的信息
select snum.cnum,score from sc where score > (select score from sc where snum = 7);
这相当于做了两个操作:
先找出学号为7的学生的成绩,为89
再查询成绩大于89的学生信息:
结果和子查询的结果一致,注意在where中使用子查询时,子查询的结果应是一个或几个值,而非一个数据表。
子查询可以在同一个表中使用,如上述的sc表中查询的例子,还可以用在不同的表中,如下例
Q7:查询成绩大于90的学生基本信息
这里提供成绩表(sc)和学生基本信息表(student1),两个表有相同的字段学号(snum),从成绩表中提取成绩大于90的学生的学号,嵌套到基本信息表中。
select * from student1 where snum in (select snum from sc where score> 90);
相关子查询的分享就到这里,具体练习可前往SQLZOO的子查询模块进行练习。
更多相关文章
- 从零到一学懂区块链(6):哈希函数
- 动画:一个底层运行函数的自白!
- 使用Eclipse实现自定义Jmeter函数助手
- Cocos平台 | 快速集成AGC云函数服务
- 正确使用异步函数的姿势[每日前端夜话(0x06)]
- Numpy中Meshgrid函数介绍及2种应用场景
- Python中匿名函数与内置高阶函数详解
- 面试必知必会|理解C++虚函数
- 类和函数傻傻分不清楚?三个例子讲明白