oracle nullif函数的使用

//题目：   

 ID NAME     

------------  

 1   张  

 1   王  

 2   赵  

//结果：   

ID NAME  

--------  

1   张  

    王  

2   赵  

//解法一：   

with t as(  

     select 1 id, '张' name from dual union all  

     select 1 id, '王' name from dual union all  

     select 1 id, '李' name from dual union all  

     select 2 id, '赵' name from dual)  

SELECT nullif(id, pid) id, NAME  

FROM (  

     SELECT id, NAME,  

            lag(id) over(PARTITION BY id ORDER BY id) pid  

     FROM t)  

        ID NAME  

---------- ----  

         1 张  

           王  

           李  

         2 赵  

--  

//分析：   

//这里巧妙的利用了lag()分析函数，   

//此函数可一次性的返回多行数据，而不需要进行表的自连接   

//而且可以将距离第一行记录几行的记录返回，若没有记录就返回null   

//下面是嵌套视图的查询结果：   

SELECT id, NAME,  

       lag(id) over(PARTITION BY id ORDER BY id) pid  

FROM t  

  /  

        ID NAME        PID  

---------- ---- ----------  

         1 张     

         1 王            1  

         1 李            1  

         2 赵    

//我们再来看外层查询，即主查询   

//这里使用到了nullif()函数，本人还是头一次看到此函数   

//这个函数有两个参数，nullif(x,y)，   

//它将x和y进行比较，如果x=y，则返回值为null，如果不等，则返回x   

For example:  

NULLIF(12, 12)  would return NULL  

NULLIF(12, 13)  would return 12  

NULLIF('apples', 'apples')  would return NULL  

NULLIF('apples', 'oranges') would return 'apples'  

//   

//解法二：   

with tt as(  

     select 1 id, '张' name from dual union all  

     select 1 id, '王' name from dual union all  

     select 1 id, '李' name from dual union all  

     select 2 id, '赵' name from dual)  

select decode(row_number() over(partition by id order by id),1,id) id,  

       name  

from tt  

        ID NAME  

---------- ----  

         1 张  

           王  

           李  

         2 赵  

//解析：   

//此解法是用到了窗口函数row_number()分析函数，   

//此函数是为每一个分组和排序返回一个排序值，用法有点像rownum   

//我们来看看如果没用decode函数结果是怎么样的：   

select row_number() over(partition by id order by id) id,  

       name  

from tt  

        ID NAME  

---------- ----  

         1 张  

         2 王  

         3 李  

         1 赵  

//使用decode函数是为了进行比较的，   

//如果row_number函数返回的id值为1，那么就显示此id值   

//如果row_number函数返回的id值不为1，那么就显示null   

//所以我们看到了上面的结果:张的id为1，赵的id也为1   

原帖：

http://topic.csdn.net/u/20110511/17/22dcbc7b-e8d1-4c77-a4de-1ecca939fb24.html?50950

oracle nullif()函数

http://www.techonthenet.com/oracle/functions/nullif.php

oracle lag()分析函数:

http://blog.csdn.net/BOBO12082119/archive/2011/03/30/6289648.aspx

oracle row_number()分析函数:

http://blog.csdn.net/BOBO12082119/archive/2011/04/01/6294889.aspx