Sql函数的用法(记)

1.WITH AS短语，也叫做子查询部分，定义一个SQL片断后，该SQL片断可以被整个SQL语句所用到。有的时候，with as是为了提高SQL语句的可读性，减少嵌套冗余。

ex:

with c as (
  select  * 
  from user
) 
select * 
from c, emailuser 
where 
  emailuser.userid = c.id**

2.case when的使用方法

第一种 格式 : 简单Case函数 :

格式说明

case 列名

when 条件值1 then 选项1

when 条件值2 then 选项2…

else 默认值 end

eg:

　　　　select 
　　　　case 　　user_level
　　　　when     'A'     then    'AAA'
　　　　when　  'B'     then    'BBB'
　　　　when　  'C'     then    'CCC'
　　　　else       'eee' end
　　　　from     emailUser

第二种 格式 :Case搜索函数

格式说明

case

when 列名= 条件值1 then 选项1

when 列名=条件值2 then 选项2…

else 默认值 end

eg:

　　　　update  employee
　　　　set         e_wage =
　　　　case
　　　　when   job_level = '1'    then e_wage*1.2
　　　　when   job_level = '2'   then e_wage*1.3
　　　　when   job_level = '3'   then e_wage*1.4
　　　　else     e_wage*1.5
　　　　end

3.NVL函数的功能是实现空值的转换，根据第一个表达式的值是否为空值来返回相应的列名或表达式，主要用于对数据列上的空值进行处理，语法格式如：NVL( string1, replace_with)。

eg
下面两行代码表示 如果sum(t.dwxhl) = NULL 就返回 1

select nvl(sum(t.dwxhl),1)
from tb_jhde t

4.over()分析函数

说明：聚合函数（如sum()、max()等）可以计算基于组的某种聚合值，但是聚合函数对于某个组只能返回一行记录。若想对于某组返回多行记录，则需要使用分析函数。
实例：

rank()/dense_rank over(partition by ... order by ...)

over()在什么条件之上;
partition by 按哪个字段划分组(如果要分组必须，有此关键字partition)；
order by 按哪个字段排序；

使用rank()/dense_rank()时，必须要带order by否则非法

rank()/dense_rank()分级的区别：
rank(): 跳跃排序，如果有两个第一级时，接下来就是第三级。
dense_rank():连续排序，如果有两个第一级时，接下来仍然是第二级。

使用Over()函数：

SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO
  FROM (SELECT E.ENAME,
               E.JOB,
               E.SAL,
               E.DEPTNO,
               RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) RANK  --在按部门划分的基础上，工资从高到低分级，级别RANK从1开始依次递增
     FROM EMP E) E
 WHERE E.RANK = 1 ;