Oracle 语法内容

1、case 与decode 语句

 

1） CASE语句可以在SQL中实现if-then-else型的逻辑，而不必使用PL/SQL。CASE的工作方式与DECODE类似，但应该使用CASE，因为它与标准SQL兼容。

  CASE有两种形式:

  a）简单CASE语句，使用表达式确定返回值。语法：

       CASE 表达式
           WHEN  值1  THEN  结果1
           WHEN  值2  THEN  结果2
           ...
           WHEN  值N  THEN  结果N
           ELSE  缺省值
       END

  b）搜索CASE语句，使用条件确定返回值。语法：

        CASE

           WHEN  条件1  THEN  结果1
           WHEN  条件2  THEN  结果2
            ...
           WHEN  条件N  THEN  结果N
           ELSE  缺省值

        END

2）DECODE函数是ORACLE  PL/SQL中功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。语法：

     DECODE(条件，值1，翻译值1，值2，翻译值2，...，值n，翻译值n，缺省值)

3）CASE能自动去掉字符右边的空格（左边的空格就去不了，此时得用trim函数），但DECODE不行。

比如：select  case 'A '      --在A的右边有一个空格，此时查询结果为'a'
                        when 'A' then 'a' 
                     end
         from dual;

         select  decode('A ','A','a')  --在第一个A的右边有一个空格，此时查询无结果集
         from dual;

 

2、row_number ()、 rank ()和 dense_rank ()

     伪列rownum无法实现按分组排序，此时可以使用以下三个函数：

     row_number()/ rank()/ dense_rank()   over(partition by COL1 order by COL2)

含义：

     根据COL1分组，在分组内部根据 COL2排序，从而得到每组内部排序后的顺序编号（各个分组内从1开时排序）。

但三者还是有区别：

     row_number()：组内连续唯一排序---1，2，3，4

     rank()：             组内跳跃排序      ---1，1，3，4
     dense_rank()： 组内连续排序      ---1，1，2，3

 

3、求和sum() over()

     sum(col) over():等同于sum(col)
     sum(col1) over(order by col2)：求累加
     sum(col1) over (partition by col2)：求各分区的总和
     sum(col1) over (partition by col2 order by col3)：求各分区的累加

 

4、自动汇总函数rollup 和cube

     rollup是单字段汇总，cube是多字段汇总

--rollup示例

     select col1 ,sum(col2)

     from table_name
     group by rollup(col1)；

--cube示例    

     select col1,col2,sum(co3)

     from table_name
     group by cube(col1,col2)；