一.group by 分组函数的使用及其注意项
二.case when函数的简单用法
三.对表进行分组并排序取前几名的方法
(一).group by 分组函数的使用及其注意项
在学习sql时group by是我们经常使用的方法,也是非常重要的方法·,起方法使用如下
select (列名)count(*) for 表名 group by (列名)name (having ***)
表示通过name列对此表进行分组 并统计此表有多少个人名,不过在使用group by的时候要注意通过列名对表进行分组之后,不能在查询到通过列名被折叠起来的数据,如
select sid,avg(cscore) from score group by sid;
意味通过sid对score表进行分组
如果此时想查询折叠起来的cscore就会报错,但是却可以对隐藏数据的整体进行操作,如上可以找到每个分组的平均值,如果如下
select sid,avg(cscore),max(cscore) from score group by sid;
查询各个分组的最高值也行,所以在进行group by分组之后 折叠起来的信息是一个整体,可以对整体进行操作,此时使用聚合函数也是可以的但无法对个体数值进行操作。
在使用group by函数之后 进行的条件查询是having 关键字
SELECT ss.Sname,avg(cscore) FROM score s
LEFT JOIN student ss on s.sid = ss.sid
GROUP BY ss.Sname
HAVING avg(cscore)>=60
having 条件筛选的是通过姓名分组之后平均值大于60的 人。
(二)case when函数的简单用法
一般有两种用法
(1)case when. 字段 then 输出
when 字段 then 输出
.....
else
end
这个的使用就很像java基础的基础判断一样when就代表这如果...*** then 就表示为 就****
先用如下代码理解一下
SELECT ss.ename,ss.sc ,case
when ss.sc >500 and ss.sc<1000 then 'P1'
when ss.sc>=1000 and ss.sc<2000 then 'P2'
when ss.sc >=2000 and ss.sc <3000 then 'P3'
when ss.sc >=3000 then 'p4' end pinji
FROM (SELECT ename,s.sal+s.comm sc FROM (SELECT ename ,sal,ifnull(comm ,'0') comm FROM emp) s) ss
搜索结果如下
在case when的使用在如上代码中是通过when对sc列表进行条件书筛选再通过then p1 显示出在符合when条件时 在后面输出p1,when *** then *** 可以多次使用,实现多条件筛选,当然在最好还可以使用else 就如同java基础的 if else条件语句一样,当别筛选数据都不满足when的条件时运行else 输出, ( 注意 在最后一点要加end 表示结束)
(三).对表进行分组并排序取前几名的方法
SELECT * FROM emp e1 WHERE ( SELECT COUNt(*) FROM emp e2 WHERE e1.deptno=e2.deptno and e1.sal<e2.sal ) <3 ORDER BY deptno , sal DESC
这个代码的作业是对deptno进行分组 在提取出每个组里sal最高的三个人 最好通过order by 对deptno 和 sal进行排序,现在我们开始对这串代码进行分析。
首先 我们先写 where 子查询
SELECT * FROM emp e2 WHERE e2.deptno=10 and 1500<e2.sal
这是执行其中子查询的结果,显示的是在表中deptno =10 且sal高于1500的人群,这个查询是很简单的 那么我们现在引入第二个表
SELECT * FROM emp e1 WHERE ( SELECT COUNt(*) FROM emp e2 WHERE e1.deptno=e2.deptno and e1.sal<e2.sal ) <3
我们现在就是引入表e1 这还emp的完整的原表,现在子查询
SELECT * FROM emp e1 WHERE ( SELECT COUNt(*) FROM emp e2 WHERE e1.deptno=e2.deptno and e1.sal<e2.sal ) =0
就是表示的是 将两个emp表进行对比,对比的条件就是两个表e1与e2的deptno相同 并且 e1的sal小于e2的 ,=0 表示的是在e1抽取到某一行时在deptno相同的e2的中对比每一条sal,当e2中大于e1中sal的个数是0的就被输出 ,这也表示该条e1的行在同deptno中是最大的。
SELECT * FROM emp e1 WHERE ( SELECT COUNt(*) FROM emp e2 WHERE e1.deptno=e2.deptno and e1.sal<e2.sal ) <3
也就是在e2中大于该条e1的数列小于三 也就是第一名大于数为0 ,小于3所以符合,第二名,统计数为1,小于3所以符合,所以这个方法可以对一个表进行自连接并取出特定范围值的数据。