oracle sql查询使用函数

查询过程中经常使用复杂的函数才能达到目的

1、nvl

NVL( A, B) 　　
    功能：如果A为NULL，则NVL函数返回B的值，否则返回A的值。 　　
    注意事项：A和B必须为同一数据类型，除非显示的使用TO_CHAR函数。
    例：NVL(TO_CHAR(num1),str2 ) 其中num1代指某个数字类型的值。 　  

NVL的扩展-NVL2(A,B,C)
   Oracle在NVL函数的功能上扩展，提供了NVL2函数。 　　

   NVL2(A, B, C)的功能为：如果A为NULL，则函数返回C，若A不为   null，则返回B。

2、case

(1)case when的两种表述方式

--简单Case函数  

CASE sex  
WHEN '1' THEN '男'  
WHEN '2' THEN '女'  
ELSE '其他' END  

--Case搜索函数  

CASE
WHEN sex = '1' THEN '男'  
WHEN sex = '2' THEN '女'  
ELSE '其他' END

（2）CASE WHEN 在语句中不同位置的用法

SELECT   grade, COUNT (CASE WHEN sex = 1 THEN 1      /*sex 1为男生，2位女生*/
                       ELSE NULL
                       END) 男生数,
                COUNT (CASE WHEN sex = 2 THEN 1
                       ELSE NULL
                       END) 女生数
    FROM students GROUP BY grade;

SELECT T2.*, T1.*
   FROM T1, T2
  WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND
                   T1.SOME_TYPE LIKE 'NOTHING%'
                THEN 1
              WHEN T2.COMPARE_TYPE != 'A' AND
                   T1.SOME_TYPE NOT LIKE 'NOTHING%'
                THEN 1
              ELSE 0
           END) = 1

3、if

（1）if(exp,A,B)

select if(exp,A,B) from table;

当exp=true，返回A，否则返回B

与nvl的区别是，nvl是判断是否为null,而if判断是否为true

(2)if else

4、wm_concat函数
 

函数wm_concat(列名)可以把列值以","号分隔起来,并显示成一行
 
准备测试数据
 
SQL>createtabletest(id number,namevarchar2(20));
 
SQL>insertintotest values(1,'a');
 
SQL>insertintotest values(1,'b');
 
SQL>insertintotest values(1,'c');
 
SQL>insertintotest values(2,'d');
 
SQL>insertintotest values(2,'e');
 
SQL>commit;
 

 

效果1 :列转行
 
SQL>selectwm_concat(name)fromtest;
 
WM_CONCAT(NAME)
 
------------------------------
 
a,b,c,d,e
 

 

效果2: 把结果里的逗号替换成"|"
 
SQL>selectreplace(wm_concat(name),',','|')fromtest;
 
REPLACE(WM_CONCAT(NAME),',','|')
 
-----------------------------------------------------------------------
 
a|b|c|d|e
 

 

效果3:按ID分组合并name
 
SQL>selectid,wm_concat(name)namefromtest groupbyid;
 
IDNAME
 
---------- ------------------------------
 
1 a,b,c
 
2 d,e

5、行转列

使用case语句

select

 nvl(sum(case when ec_type = 'successuv' then cnt end),0) "兑换成功用户数"
,nvl(sum(case when ec_type = 'successpv' then cnt end),0) "兑换成功次数"
,nvl(sum(case when ec_type = 'failuv' then cnt end),0) "兑换失败用户数"
,nvl(sum(case when ec_type = 'failpv' then cnt end),0) "兑换失败次数"

from table