求个sql语句

Question

with t as
 (select '001' bh, 0 bz
    from dual
  union all
  select '002' bh, 1 bz
    from dual
  union all
  select '003' bh, 1 bz
    from dual
  union all
  select '004' bh, 1 bz
    from dual
  union all
  select '005' bh, 0 bz
    from dual
  union all
  select '006' bh, 1 bz
    from dual
  union all
  select '007' bh, 1 bz
    from dual
  union all
  select '008' bh, 1 bz
    from dual
  union all
  select '009' bh, 1 bz
    from dual
  union all
  select '010' bh, 0 bz
    from dual)
SELECT to_char(wm_concat(str)), bz
  from (SELECT S_BH || '-' || E_BH STR, BZ
          FROM (SELECT T3.bh S_bh,
                       NVL((SELECT MAX(bh)
                             FROM T
                            WHERE T.bh >= T3.bh
                              AND T.bh < NVL(T3.N_bh, T.bh + 1)),
                           T3.bh) E_BH,
                       T3.bz
                  FROM (SELECT T2.*, LEAD(T2.bh) OVER(ORDER BY T2.bh) N_bh
                          FROM (SELECT T1.*, LAG(bz) OVER(ORDER BY T1.bh) P_bz
                                  FROM T T1
                                 ORDER BY bh) T2
                         WHERE T2.P_bz IS NULL
                            OR T2.P_bz <> T2.bz) T3
                 WHERE T3.BZ = 1))
 group by bz;

Answer 1

select a.bh||'-'||b.bh
from test a,test b
where b.bh in  (select bh from test connect by prior bh+1=bh and prior bz=bz start with bh=a.bh+1)
and exists (select 1 from test where bh=b.bh+1 and bz<>b.bz)
and exists (select 1 from test where bh=a.bh-1 and bz<>a.bz)
and a.bz='1'

Answer 2

蛮有意思的一个需求，所以实际建表测试了下
上面语句，在边缘时存在bug， 如 001-010为1时会查不出来
语句重新调整了下，已测试通过
select a.bh||'-'||b.bh
from test a,test b
where b.bh in  (select bh from test connect by prior bh+1=bh and prior bz=bz start with bh=a.bh+1)
and not exists (select 1 from test where bh=b.bh+1 and bz=b.bz)
and not exists (select 1 from test where bh=a.bh-1 and bz=a.bz)
and a.bz='1'

Answer 3

哥儿们，你这个还是有点问题（009-010），你可以用这种测试数据执行就能看的问题with test as
 (select '001' bh, 0 bz
    from dual
  union all
  select '002' bh, 1 bz
    from dual
  union all
  select '003' bh, 1 bz
    from dual
  union all
  select '004' bh, 1 bz
    from dual
  union all
  select '005' bh, 0 bz
    from dual
  union all
  select '006' bh, 1 bz
    from dual
  union all
  select '007' bh, 1 bz
    from dual
  union all
  select '008' bh, 0 bz
    from dual
  union all
  select '009' bh, 1 bz
    from dual
  union all
  select '010' bh, 0 bz from dual)
select a.bh||'-'||b.bh
from test a,test b
where b.bh in  (select bh from test connect by prior bh+1=bh and prior bz=bz start with bh=a.bh+1)
and not exists (select 1 from test where bh=b.bh+1 and bz=b.bz)
and not exists (select 1 from test where bh=a.bh-1 and bz=a.bz)
and a.bz='1'

Answer 4

哥儿们，你这个还是有点问题（009-010），你可以用这种测试数据执行就能看的问题with test as
 (select '001' bh, 0 bz
    from dual
  union all
  select '002' bh, 1 bz
    from dual
  union all
  select '003' bh, 1 bz
    from dual
  union all
  select '004' bh, 1 bz
    from dual
  union all
  select '005' bh, 0 bz
    from dual
  union all
  select '006' bh, 1 bz
    from dual
  union all
  select '007' bh, 1 bz
    from dual
  union all
  select '008' bh, 0 bz
    from dual
  union all
  select '009' bh, 1 bz
    from dual
  union all
  select '010' bh, 0 bz from dual)
select a.bh||'-'||b.bh
from test a,test b
where b.bh in  (select bh from test connect by prior bh+1=bh and prior bz=bz start with bh=a.bh+1)
and not exists (select 1 from test where bh=b.bh+1 and bz=b.bz)
and not exists (select 1 from test where bh=a.bh-1 and bz=a.bz)
and a.bz='1'

确实存在问题，原因已找到，本来想提升点速度，结果弄出问题来了，改为start with bh=a.bh应该就可以了
select a.bh||'-'||b.bh
from test a,test b
where b.bh in  (select bh from test connect by prior bh+1=bh and prior bz=bz start with bh=a.bh)
and not exists (select 1 from test where bh=b.bh+1 and bz=b.bz)
and not exists (select 1 from test where bh=a.bh-1 and bz=a.bz)
and a.bz='1'