求某表状态变化时，记录下之前之后编号的位置。

本问题由：https://bbs.csdn.net/topics/392280779?page=1#post-411115296 而引发。

现有一个tb_sqsts表，其结构是：

create table tb_sqsts(
    id number(4,0) not null primary key,
    seq number(3,0) not null,
    status number(2,0) not null)

其中填充了这些值：

insert into tb_sqsts(id,seq,status) values('1','1','1');
insert into tb_sqsts(id,seq,status) values('2','3','1');
insert into tb_sqsts(id,seq,status) values('3','7','1');
insert into tb_sqsts(id,seq,status) values('4','5','1');
insert into tb_sqsts(id,seq,status) values('5','4','0');
insert into tb_sqsts(id,seq,status) values('6','9','0');
insert into tb_sqsts(id,seq,status) values('7','16','0');
insert into tb_sqsts(id,seq,status) values('8','12','1');
insert into tb_sqsts(id,seq,status) values('9','10','1');
insert into tb_sqsts(id,seq,status) values('10','6','1');
insert into tb_sqsts(id,seq,status) values('11','2','0');
insert into tb_sqsts(id,seq,status) values('12','22','0');

然后我们查看一下结果：

SQL> select * from tb_sqsts order by seq;

        ID        SEQ     STATUS
---------- ---------- ----------
        11          2          0
         2          3          1
         5          4          0
         4          5          1
        10          6          1
         3          7          1
         6          9          0
         9         10          1
         8         12          1
         7         16          0
        12         22          0

已选择11行。

现在要求，在status发生变化时，将seq顺序编号记录下来。

期望值就是2-3，3-4，4-5，7-9，9-10，12-16.

 

需求的难点在于seq并不连续，如果它是连续的，那么seq+1就知道下一条记录，继而知道status值变没变化了。

我们借助oracle的row_number()函数或是rank()函数给这张表添加一个连续的列就好了。

于是有了下面的sql

SQL> select row_number() over (order by seq) as rn,a.* from tb_sqsts a;

        RN         ID        SEQ     STATUS
---------- ---------- ---------- ----------
         1         11          2          0
         2          2          3          1
         3          5          4          0
         4          4          5          1
         5         10          6          1
         6          3          7          1
         7          6          9          0
         8          9         10          1
         9          8         12          1
        10          7         16          0
        11         12         22          0

已选择11行。

然后以新结果集进行自联结，找出那些rn递增而status不同的记录即可：

SQL> select a.seq||'-'||b.seq from
  2  (select row_number() over (order by seq) as rn,tb_sqsts.* from tb_sqsts ) a
  3  left join (select row_number() over (order by seq) as rn,tb_sqsts.* from tb_sqsts ) b
  4  on b.rn=a.rn+1
  5  where b.rn is not null
  6  and a.status<>b.status;

A.SEQ||'-'||B.SEQ
---------------------------------------------------------------------------------
2-3
3-4
4-5
7-9
9-10
12-16

已选择6行。

已用时间:  00: 00: 00.00

从结果看，与期望值一致。

因此，达成需求的最终SQL就是：

select a.seq||'-'||b.seq from
(select row_number() over (order by seq) as rn,tb_sqsts.* from tb_sqsts ) a 
left join (select row_number() over (order by seq) as rn,tb_sqsts.* from tb_sqsts ) b
on b.rn=a.rn+1 
where b.rn is not null
and a.status<>b.status

--2020年3月28日--

上面使用到的全部SQL文：

create table tb_sqsts(
    id number(4,0) not null primary key,
    seq number(3,0) not null,
    status number(2,0) not null)
    
    
insert into tb_sqsts(id,seq,status) values('1','1','1');
insert into tb_sqsts(id,seq,status) values('2','3','1');
insert into tb_sqsts(id,seq,status) values('3','7','1');
insert into tb_sqsts(id,seq,status) values('4','5','1');
insert into tb_sqsts(id,seq,status) values('5','4','0');
insert into tb_sqsts(id,seq,status) values('6','9','0');
insert into tb_sqsts(id,seq,status) values('7','16','0');
insert into tb_sqsts(id,seq,status) values('8','12','1');
insert into tb_sqsts(id,seq,status) values('9','10','1');
insert into tb_sqsts(id,seq,status) values('10','6','1');
insert into tb_sqsts(id,seq,status) values('11','2','0');
insert into tb_sqsts(id,seq,status) values('12','22','0');

select * from tb_sqsts order by seq

select row_number() over (order by seq) as rn,a.* from tb_sqsts a

select a.seq||'-'||b.seq from
(select row_number() over (order by seq) as rn,tb_sqsts.* from tb_sqsts ) a 
left join (select row_number() over (order by seq) as rn,tb_sqsts.* from tb_sqsts ) b
on b.rn=a.rn+1 
where b.rn is not null
and a.status<>b.status