--1、建表 create table ticket as select * from( select 'A' id, 1 from_no,3 to_no from dual union all select 'A' id, 4 from_no,8 to_no from dual union all select 'B' id, 1 from_no,9 to_no from dual union all select 'B' id, 10 from_no, 20 to_no from dual union all select 'C' id, 12 from_no, 19 to_no from dual union all select 'C' id, 22 from_no, 40 to_no from dual);
--2、合并连号 WITH t AS (SELECT id, from_no, to_no, lag(to_no, 1, 0) over(PARTITION BY id ORDER BY from_no) prev_tono FROM ticket) SELECT * FROM (SELECT id, MIN(from_no) from_no, MAX(to_no) to_no FROM t WHERE from_no = prev_tono + 1 GROUP BY id UNION ALL SELECT id, from_no, to_no FROM t WHERE from_no <> prev_tono + 1) ORDER BY id, from_no;
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
是很强大,但是速度感觉太慢啊,如果再有条件应该怎么加,where?
把整个语句做为子查询,如: select * from (上面的语句) where ...有多少数据,如果慢的话,可以优化一下嘛或者把条件拿出来看看,
我没有说明白 数据结构如下: 票符 起号 至号 A 1 3 A 4 8 B 1 9 B 10 20 C 12 19 C 22 40想得到的结果是: A 1 8 B 1 20 C 12 19 C 22 40 谢谢!其中还有几列,单位,日期,如果查某个单位某个日期段的数据应该怎么写。呵呵
引用 4 楼 wildwave 的回复: 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 如果加2个条件,单位=办公室 日期=20100310 应该怎么写呢?谢谢
select 票符, min(起号), max(至号) from table1 t where 单位 = '办公室' and 日期 = '20100310' start with not exists (select 1 from table1 where 至号 = t.起号 - 1 and 票符 = t.票符 and 单位 = t.单位 and 日期 = t.日期) connect by prior 至号 = 起号 - 1 and prior 票符 = 票符 group by 票符, rownum - level
W 48815 48867 W 50842 50940 W 50860 50940 W 48828 48867 W 48857 48867 W 50922 50940 W 48861 48867 W 50932 50940 Z 25001 25054 Z 25026 25054结果不对啊?怎么是这样呢? Z 25001 25054 Z 25026 25054高人帮忙看看啊
select 票符, min(起号), max(至号) from table1 t where 单位 = '办公室' and 日期 = '20100310' start with not exists (select 1 from table1 where 至号 = t.起号 - 1 and 票符 = t.票符 and 单位 = t.单位 and 日期 = t.日期) connect by prior t.至号 = t.起号 - 1 and prior t.票符 = t.票符 group by 票符, rownum - levelconnect by prior t.至号 = t.起号 - 1 and prior t.票符 = t.票符 group by 票符, rownum - level这样就可以了,但是执行速度特别慢,应该如何优化呢?
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
create table ticket as select * from(
select 'A' id, 1 from_no,3 to_no from dual union all
select 'A' id, 4 from_no,8 to_no from dual union all
select 'B' id, 1 from_no,9 to_no from dual union all
select 'B' id, 10 from_no, 20 to_no from dual union all
select 'C' id, 12 from_no, 19 to_no from dual union all
select 'C' id, 22 from_no, 40 to_no from dual);
--2、合并连号
WITH t AS
(SELECT id, from_no, to_no, lag(to_no, 1, 0) over(PARTITION BY id ORDER BY from_no) prev_tono
FROM ticket)
SELECT *
FROM (SELECT id, MIN(from_no) from_no, MAX(to_no) to_no
FROM t
WHERE from_no = prev_tono + 1
GROUP BY id
UNION ALL
SELECT id, from_no, to_no FROM t WHERE from_no <> prev_tono + 1)
ORDER BY id, from_no;
start with not exists(
select 1 from table1 where 至号=t.起号-1)
connect by prior 至号=起号-1
and prior 票符=票符
group by 票符,rownum-level
select * from (上面的语句) where ...有多少数据,如果慢的话,可以优化一下嘛或者把条件拿出来看看,
数据结构如下:
票符 起号 至号
A 1 3
A 4 8
B 1 9
B 10 20
C 12 19
C 22 40想得到的结果是:
A 1 8
B 1 20
C 12 19
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
如果加2个条件,单位=办公室 日期=20100310 应该怎么写呢?谢谢
from table1 t
where 单位 = '办公室'
and 日期 = '20100310'
start with not exists (select 1
from table1
where 至号 = t.起号 - 1
and 票符 = t.票符
and 单位 = t.单位
and 日期 = t.日期)
connect by prior 至号 = 起号 - 1
and prior 票符 = 票符
group by 票符, rownum - level
W 50842 50940
W 50860 50940
W 48828 48867
W 48857 48867
W 50922 50940
W 48861 48867
W 50932 50940
Z 25001 25054
Z 25026 25054结果不对啊?怎么是这样呢?
Z 25001 25054
Z 25026 25054高人帮忙看看啊
from table1 t
where 单位 = '办公室'
and 日期 = '20100310'
start with not exists (select 1
from table1
where 至号 = t.起号 - 1
and 票符 = t.票符
and 单位 = t.单位
and 日期 = t.日期)
connect by prior t.至号 = t.起号 - 1
and prior t.票符 = t.票符
group by 票符, rownum - levelconnect by prior t.至号 = t.起号 - 1
and prior t.票符 = t.票符
group by 票符, rownum - level这样就可以了,但是执行速度特别慢,应该如何优化呢?
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