myspl 常见函数

一.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所以符合，所以这个方法可以对一个表进行自连接并取出特定范围值的数据。