oracle断号边界查询问题

Question

因业务需要，需要解决以下问题：

有表如图：

需要在status=0的条件下统计出如下结果：

min(pwlock)	max(pwlock)	num_prefix	teller_no
6	7	YY999999999	444444
5	8	WW999999999	222222
5	5	YY999999999	222222
8	10	YY999999999	222222
1	4	WW999999999	333333
9	10	WW999999999	333333

尝试过一些断号查询及分组查询的方法，不尽人意。

如：

select b.teller_no, min(b.pwlock), max(b.pwlock), b.num_prefix
  from (select a.*, to_number(a.pwlock - rownum) cc
          from (select * from fm_pwlockuse order by pwlock) a
         where status = '0') b
 group by b.teller_no, b.cc, b.num_prefix

但得出的结果还是有偏差：

希望能指导下如何编写这条sql。

Answer 1

正好刚刚在另一个问题里写了个类似的,改了一下

with t(pwlock,status,teller_no,num_prefix) as (
select 2,1,222222,'YY' from dual union all
select 3,1,222222,'YY' from dual union all
select 4,1,222222,'YY' from dual union all
select 5,0,222222,'YY' from dual union all
select 5,0,222222,'WW' from dual union all
select 6,0,222222,'WW' from dual union all
select 7,0,222222,'WW' from dual union all
select 8,0,222222,'YY' from dual union all
select 8,0,222222,'WW' from dual union all
select 9,0,222222,'YY' from dual union all
select 10,0,222222,'YY' from dual union all
select 1,0,333333,'WW' from dual union all
select 2,0,333333,'WW' from dual union all
select 3,0,333333,'WW' from dual union all
select 4,0,333333,'WW' from dual union all
select 9,0,333333,'WW' from dual union all
select 10,0,333333,'WW' from dual union all
select 1,1,444444,'YY' from dual union all
select 6,0,444444,'YY' from dual union all
select 7,0,444444,'YY' from dual  )
,T2 AS (
select T.*,ROW_NUMBER() OVER(PARTITION BY teller_no,num_prefix ORDER BY PWLOCK) RN from T 
),
T3(pwlock,status,teller_no,num_prefix) AS (
SELECT pwlock,status,teller_no,num_prefix FROM T2 WHERE T2.RN=1
UNION ALL
SELECT T3.pwlock+1,NVL(T2.status,1),T3.teller_no,T3.num_prefix FROM T3 LEFT JOIN T2 ON
 T3.teller_no=T2.teller_no AND T3.num_prefix=T2.num_prefix AND  T3.PWLOCK=T2.PWLOCK-1
 WHERE T3.PWLOCK<=10
),
T4 AS (
select PWLOCK,STATUS,TELLER_NO,NUM_PREFIX,cnt,
LAG(PWLOCK,CASE WHEN CNT=0 THEN 0 ELSE CNT-1 END ) OVER(PARTITION BY TELLER_NO,NUM_PREFIX ORDER BY PWLOCK) L from t3 
match_recognize(
   partition by TELLER_NO,NUM_PREFIX
  order by PWLOCK
  measures  count(decode(STATUS,1,null,1)) cnt
  ALL ROWS PER MATCH
  pattern(a* b)
  define a as STATUS = next(STATUS)
  ))
  select MIN(PWLOCK),MAX(PWLOCK),TELLER_NO, NUM_PREFIX from T4 WHERE CNT>0 GROUP BY L,TELLER_NO, NUM_PREFIX