数据结构如下:
单位 日期 票符 起号 至号
aaa 20100101 A 1 3
aaa 20100101 A 4 8
bbb 20100102 B 1 9
bbb 20100102 B 10 20
ddd 20100103 C 12 19
ddd 20100103 C 22 40根据条件(单位=aaa and rq=2010010)想得到如下的结果是:
aaa 20100101 A 1 8
以下类似
bbb 20100102 B 1 20
ddd 20100103 C 12 19
ddd 20100103 C 22 40
谢谢!select 票符,min(起号),max(至号) from table1 t
start with not exists(
select 1 from table1 where 至号=t.起号-1)
connect by prior 至号=起号-1
and prior 票符=票符
group by 票符,rownum-level有高人给我提供一段这样的代码,但是如何加上条件呢?单位=aaa and rq=2010010
谢谢
单位 日期 票符 起号 至号
aaa 20100101 A 1 3
aaa 20100101 A 4 8
bbb 20100102 B 1 9
bbb 20100102 B 10 20
ddd 20100103 C 12 19
ddd 20100103 C 22 40根据条件(单位=aaa and rq=2010010)想得到如下的结果是:
aaa 20100101 A 1 8
以下类似
bbb 20100102 B 1 20
ddd 20100103 C 12 19
ddd 20100103 C 22 40
谢谢!select 票符,min(起号),max(至号) from table1 t
start with not exists(
select 1 from table1 where 至号=t.起号-1)
connect by prior 至号=起号-1
and prior 票符=票符
group by 票符,rownum-level有高人给我提供一段这样的代码,但是如何加上条件呢?单位=aaa and rq=2010010
谢谢
start with not exists(
select 1 from table2 where 至号=t.起号-1)
connect by prior 至号=起号-1
and prior 票符=票符
group by 票符,rownum-level
with aaa as
(select 'aaa' 单位 ,'20100101' 日期,'A'票符 , 1 起号 , 3 至号 from dual union all
select 'aaa','20100101','A' ,4, 8 from dual union all
select 'bbb','20100102','B' ,1, 9 from dual union all
select 'bbb','20100102','B' ,4, 20 from dual union all
select 'ddd','20100103','C' ,12, 19 from dual union all
select 'ddd','20100103','C' ,22, 40 from dual),
b as (
select a.单位,a.日期,a.票符,level ,CONNECT_BY_ROOT a.起号 起号,a.至号 至号,CONNECT_BY_ISLEAF leaf
from aaa a
start with not exists (select 'X'
from aaa a2
where a2.票符=a.票符
and a2.至号=a.起号-1
)
connect by prior a.票符=a.票符
and prior a.至号+1=a.起号
)
select b.单位,b.日期,b.票符,min(b.起号),b.至号
from b
where b.leaf=1
group by b.票符,b.至号,b.单位,b.日期
(select 'aaa' 单位 ,'20100101' 日期,'A'票符 , 1 起号 , 3 至号 from dual union all
select 'aaa','20100101','A' ,4, 8 from dual union all
select 'bbb','20100102','B' ,1, 9 from dual union all
select 'bbb','20100102','B' ,4, 20 from dual union all
select 'ddd','20100103','C' ,12, 19 from dual union all
select 'ddd','20100103','C' ,22, 40 from dual),
b as (
select a.单位,a.日期,a.票符,level ,CONNECT_BY_ROOT a.起号 起号,a.至号 至号,CONNECT_BY_ISLEAF leaf
from aaa a
start with not exists (select 'X'
from aaa a2
where a2.票符=a.票符
and a2.至号=a.起号-1
)
and 单位='aaa'
and 日期='20100101'
connect by prior a.票符=a.票符
and prior a.至号+1=a.起号
)
select b.单位,b.日期,b.票符,min(b.起号),b.至号
from b
where b.leaf=1
group by b.票符,b.至号,b.单位,b.日期
把下面的aaa换成实际的表名就好了select b.单位,b.日期,b.票符,b.起号,b.至号
from (
select a.单位,a.日期,a.票符,level ,CONNECT_BY_ROOT a.起号 起号,a.至号 至号,CONNECT_BY_ISLEAF leaf
from aaa a
start with not exists (select 'X'
from aaa a2
where a2.票符=a.票符
and a2.至号=a.起号-1
)
-- and 单位='aaa'
-- and 日期='20100101'
connect by prior a.票符=a.票符
and prior a.至号+1=a.起号
)b
where b.leaf=1
把下面的aaa换成实际的表名就好了
SQL code
select b.单位,b.日期,b.票符,b.起号,b.至号
from (
select a.单位,a.日期,a.票符,level ,CONNECT_BY_ROOT a.起号 起号,a.至号 至号,CONNECT_BY_ISLEAF leaf
from aaa a
start with not exists (select 'X'
from aaa a2
where a2.票符=a.票符
and a2.至号=a.起号-1
)
-- and 单位='aaa'
-- and 日期='20100101'
connect by prior a.票符=a.票符
and prior a.至号+1=a.起号
)b
where b.leaf=1 这个没有问题,就是速度特别慢,应该如何优化呢?