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

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

SQL> WITH A AS (SELECT 1 ID,'A' NAME FROM DUALITPUB个人空间X`~v8D#wmYp4n
  2             UNION ALL
jc7xd)Q5U$Gx0  3             SELECT 1 ID,'B' NAME FROM DUAL
T@I0q@:PhW{ Y Z0  4             UNION ALL
WeboJ)O]w$y7|0  5             SELECT 1 ID,'C' NAME FROM DUALITPUB个人空间g)[
HbA,q1bT8y(dgH
  6             UNION ALLITPUB个人空间
[&e5u9L w`2~
  7             SELECT 2 ID,'D' NAME FROM DUALITPUB个人空间R AR-^YM1S
  8             UNION ALL
v atW"^0GQ%A0  9             SELECT 2 ID,'E' NAME FROM DUAL
T A7n$E ?0\RW7]$c,\0 10             UNION ALL
lJ(` GYyjiyMr~0 11             SELECT 2 ID,'F' NAME FROM DUALITPUB个人空间qW?a9?+R7u;y#X8}
 12             )
6a+UF|:r6\0 13  select decode(lag(A.id) over(order by A.id),A.id,to_number(null),A.id) as newid,ITPUB个人空间Ou!F"I |RMz,tA
 14         A.name
y-i!{d]!n u0 15    from A;
?gp9tJ'@0 ITPUB个人空间#y_hV8L2n-B:QJP
     NEWID NAMEITPUB个人空间&z1wf&s'kR
---------- ----
MJ@%}O;U0         1 AITPUB个人空间D|#XP"L8B1a kY
           BITPUB个人空间U W7md{ H%k
           C
+V;\4WN2Q+H2Xjy0         2 D
a9U8pKR&isZN#wL0D0           E
CC ^}}P0XUR0           F
9HaNC[0 ITPUB个人空间 J"Yv#k`+\#Mk
6 rows selected

SQL> with a as (select 1 id,'王五' NAME,'0001' certified FROM DUAL
i-T6g?waK^S0  2             UNION
x;H0o3x$pq0  3             select 1 id,'王五' NAME,'0002' certified FROM DUALITPUB个人空间#tb"a:B\
  4             UNION
G${0O,G)T0  5             select 1 id,'王五' NAME,'0003' certified FROM DUALITPUB个人空间 W%a D}le"U.J7Wt
  6             UNION
9o(n6_6ksNr0  7             select 1 id,'王五' NAME,'0004' certified FROM DUAL
3j5[Z#{S
_0  8             )
c-](j:u*xB|0  9  select decode(lag(A.id) over(order by A.id,certified),A.id,to_number(null),A.id) as ID,
E:X ~X~`!EU.^#Z0 10         decode(lag(A.NAME) over(order by A.id,certified),A.NAME,to_CHAR(null),A.NAME) NAME,certified
#n djhB&jT1Y\0 11  FROM AITPUB个人空间.}6CWf I,m {S
 12  /
/P^ osx1a0 
J^_&uE/}1B0        ID NAME CERTIFIED
p[*G\Hf#Ly1t0---------- ---- ---------
7h6u*^
a&T-Er"rZ8L6b0         1 王五 0001ITPUB个人空间F!Z+[+]v q
                     0002
G,Obd3t/n]0                     0003ITPUB个人空间I.k-? MTfw'^3u
                     0004

方法二,ROW_NUMBER 分析函數

 SQL> with a as (select 1 id,'王五' NAME,'0001' certified FROM DUAL
M|8l]"\^4UE5Q0W0  2                 UNIONITPUB个人空间A"O0DB!@4N+CH*m2U
  3                 select 1 id,'王五' NAME,'0002' certified FROM DUALITPUB个人空间 R`w5vD7r+r!Q+{
  4                 UNION
7x,iY"Go-uDbP0  5                 select 1 id,'王五' NAME,'0003' certified FROM DUAL
2I A8Rs[w0  6                 UNION
J#O/[@m%|0  7                 select 1 id,'王五' NAME,'0004' certified FROM DUAL
4F;l7`d3X8MM'b0  8                 union
k3M[#EwW0  9                 select 2 id,'張三' NAME,'0001' certified FROM DUAL
!Kx#NV7e6N0 10                 UNION
f#B}hgsG0 11                 select 2 id,'張三' NAME,'0002' certified FROM DUAL
s[N(tfp
rLE V!z0 12              )
P(t9yY D&dt
^n,^0 13  select DECODE(LAG(ID)OVER(PARTITION BY ID ORDER BY ID,certified),ID,TO_NUMBER(NULL),ID) ID,ITPUB个人空间#Vni)vJ!g pe JN E7a
 14        DECODE(ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID,certified),1,NAME,NULL) NAME,
cB;Gkctjda}0 15  certified  from AITPUB个人空间faX!H/qB
 16  /
%l.J6M%R6r0W t4Tc0 ITPUB个人空间^Lxf*T
        ID NAME CERTIFIED
ov$Ju&K9U0---------- ---- ---------
r3q of-zw5cg0         1 王五 0001ITPUB个人空间%E4l ]CU%\K5m
                    0002
4C;~)h{{NCm2}0                    0003
1j9T!D_W0                    0004
|%i(X8L$zK0         2 張三 0001ITPUB个人空间*t{6d7f~il
                    0002ITPUB个人空间8XNk2Mo
Fx$K
 ITPUB个人空间 z%t8k1m#qnZ PV
6 rows selected
Fr3DRc.X9oS]o0