DECODE(LAG()OVER............) 的用法

DECODE(LAG()OVER............) 的用法

SQL> WITH A AS (SELECT 1 ID,'A' NAME FROM DUAL
  2             UNION ALL
  3             SELECT 1 ID,'B' NAME FROM DUAL
  4             UNION ALL
  5             SELECT 1 ID,'C' NAME FROM DUAL
  6             UNION ALL
  7             SELECT 2 ID,'D' NAME FROM DUAL
  8             UNION ALL
  9             SELECT 2 ID,'E' NAME FROM DUAL
 10             UNION ALL
 11             SELECT 2 ID,'F' NAME FROM DUAL
 12             )
 13  select decode(lag(A.id) over(order by A.id),A.id,to_number(null),A.id) as newid,
 14         A.name
 15    from A;
 
     NEWID NAME
---------- ----
         1 A
           B
           C
         2 D
           E
           F
 
6 rows selected

 

SQL> with a as (select 1 id,'王五' NAME,'0001' certified FROM DUAL
  2             UNION
  3             select 1 id,'王五' NAME,'0002' certified FROM DUAL
  4             UNION
  5             select 1 id,'王五' NAME,'0003' certified FROM DUAL
  6             UNION
  7             select 1 id,'王五' NAME,'0004' certified FROM DUAL
  8             )
  9  select decode(lag(A.id) over(order by A.id,certified),A.id,to_number(null),A.id) as ID,
 10         decode(lag(A.NAME) over(order by A.id,certified),A.NAME,to_CHAR(null),A.NAME) NAME,certified
 11  FROM A
 12  /
 
        ID NAME CERTIFIED
---------- ---- ---------
         1 王五 0001
                     0002
                     0003
                     0004

方法二,ROW_NUMBER 分析函數

 SQL> with a as (select 1 id,'王五' NAME,'0001' certified FROM DUAL
  2                 UNION
  3                 select 1 id,'王五' NAME,'0002' certified FROM DUAL
  4                 UNION
  5                 select 1 id,'王五' NAME,'0003' certified FROM DUAL
  6                 UNION
  7                 select 1 id,'王五' NAME,'0004' certified FROM DUAL
  8                 union
  9                 select 2 id,'張三' NAME,'0001' certified FROM DUAL
 10                 UNION
 11                 select 2 id,'張三' NAME,'0002' certified FROM DUAL
 12              )
 13  select DECODE(LAG(ID)OVER(PARTITION BY ID ORDER BY ID,certified),ID,TO_NUMBER(NULL),ID) ID,
 14         DECODE(ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID,certified),1,NAME,NULL) NAME,
 15  certified  from A
 16  /
 
        ID NAME CERTIFIED
---------- ---- ---------
         1 王五 0001
                    0002
                    0003
                    0004
         2 張三 0001
                    0002
 
6 rows selected
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10768286/viewspace-102405/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10768286/viewspace-102405/