Clear Sky - Kuru

有朋友咨询一条sql的功能实现，大致如下：

测试数据：

create table test (seq number,stat varchar2(10));

 

insert into test(seq,stat) values (1,'a');

insert into test(seq,stat) values (2,'a');

insert into test(seq,stat) values (3,'a');

insert into test(seq,stat) values (4,'b');

insert into test(seq,stat) values (5,'b');

insert into test(seq,stat) values (6,'f');

insert into test(seq,stat) values (7,'c');

insert into test(seq,stat) values (8,'c');

insert into test(seq,stat) values (9,'a');

insert into test(seq,stat) values (10,'a');

insert into test(seq,stat) values (11,'b');

insert into test(seq,stat) values (12,'a');

commit;

SQL> select * from test;

       SEQ STAT
---------- ----------
         1 a
         2 a
         3 a
         4 b
         5 b
         6 f
         7 c
         8 c
         9 a
        10 a
        11 b
        12 a

所要实现的查询功能：

按照序号依次列出stat的变化。

实现：

SELECT   seq, stat
    FROM (SELECT seq
               , stat
               , next_stat,
                 ROW_NUMBER () OVER (PARTITION BY stat, next_stat ORDER BY seq)
                   AS rn
            FROM (SELECT stat
                       , seq,
                         LEAD (stat, 1) OVER (ORDER BY seq) AS next_stat
                    FROM TEST))
   WHERE stat <> next_stat OR next_stat IS NULL OR rn = 1
ORDER BY seq;

       SEQ STAT
---------- ----------
         1 a
         3 a
         4 b
         5 b
         6 f
         7 c
         8 c
        10 a
        11 b
        12 a

小结：

此处的lead也可以通过其他sql来实现，例如：

SELECT a.stat, a.seq, b.stat
  FROM (SELECT   stat, seq, ROWNUM AS rn
            FROM TEST
        ORDER BY seq) a,
       (SELECT   stat, seq, ROWNUM AS rn
            FROM TEST
        ORDER BY seq) b
 WHERE a.rn = b.rn - 1;