表 A 数据如下:
编号 开始时间 结束时间
a 2010-1-4 8:00:00 2010-1-4 9:00:00
a 2010-1-4 8:30:00 2010-1-4 9:00:00
a 2010-1-4 9:30:00 2010-1-4 11:00:00
a 2010-1-4 10:00:00 2010-1-4 11:00:00
a 2010-1-4 13:00:00 2010-1-4 14:00:00
b 2010-1-4 8:00:00 2010-1-4 9:00:00
b 2010-1-4 8:30:00 2010-1-4 9:00:00
b 2010-1-4 8:30:00 2010-1-4 11:00:00
b 2010-1-4 10:00:00 2010-1-4 11:00:00
b 2010-1-4 13:00:00 2010-1-4 14:00:00得到如下结果:
a 2010-1-4 8:00:00 2010-1-4 9:00:00
a 2010-1-4 9:30:00 2010-1-4 11:00:00
a 2010-1-4 13:00:00 2010-1-4 14:00:00
b 2010-1-4 8:00:00 2010-1-4 11:00:00
b 2010-1-4 13:00:00 2010-1-4 14:00:00
编号 开始时间 结束时间
a 2010-1-4 8:00:00 2010-1-4 9:00:00
a 2010-1-4 8:30:00 2010-1-4 9:00:00
a 2010-1-4 9:30:00 2010-1-4 11:00:00
a 2010-1-4 10:00:00 2010-1-4 11:00:00
a 2010-1-4 13:00:00 2010-1-4 14:00:00
b 2010-1-4 8:00:00 2010-1-4 9:00:00
b 2010-1-4 8:30:00 2010-1-4 9:00:00
b 2010-1-4 8:30:00 2010-1-4 11:00:00
b 2010-1-4 10:00:00 2010-1-4 11:00:00
b 2010-1-4 13:00:00 2010-1-4 14:00:00得到如下结果:
a 2010-1-4 8:00:00 2010-1-4 9:00:00
a 2010-1-4 9:30:00 2010-1-4 11:00:00
a 2010-1-4 13:00:00 2010-1-4 14:00:00
b 2010-1-4 8:00:00 2010-1-4 11:00:00
b 2010-1-4 13:00:00 2010-1-4 14:00:00
解决方案 »
- oracle 新手求助
- 一个困惑的sql语句
- sqlldr when 语句中运算符的问题
- avg 疑问
- 现有的服务器操作系统是windows 2000 server,已经安装了sql server2000,现在想再安装Oracle 9i,这样做可行吗?
- 菜鸟求助(有两道课后习题不会做)请帮忙解解
- sqlplus work sheet登陆成功了,显示connected,但是sql语句执行不显示结果是怎么回事?
- odbc for oracle驱动程序到那下载?谢谢了!
- 我装的oracle进不了oem,出错说找不到manager server.不知该 如何解决?请高手指教
- 如何才能提取txt文件中的数据,一个表格最后要转化成txt如何实现?
- 请问为什么在oms中的作业无法删除?
- --Oracle存储过程:动态条件怎么写啊?--
- ------------------- -------------------
a 2010-01-04 08:00:00 2010-01-04 09:00:00
a 2010-01-04 08:30:00 2010-01-04 09:00:00
a 2010-01-04 09:30:00 2010-01-04 11:00:00
a 2010-01-04 10:00:00 2010-01-04 11:00:00
a 2010-01-04 13:00:00 2010-01-04 14:00:00
b 2010-01-04 08:00:00 2010-01-04 09:00:00
b 2010-01-04 08:30:00 2010-01-04 09:00:00
b 2010-01-04 08:30:00 2010-01-04 11:00:00
b 2010-01-04 10:00:00 2010-01-04 11:00:00
b 2010-01-04 13:00:00 2010-01-04 14:00:0010 rows selected.SQL> select id,to_char(btime,'yyyy-mm-dd hh24:mi:ss'),to_char(etime,'yyyy-mm-ddhh24:mi:ss') from a t
2 where not exists (select 1 from a where id=t.id and btime<=t.btime and etime>=t.etime and rowid!=t.rowid)
3 order by id,btime;I TO_CHAR(BTIME,'YYYY TO_CHAR(ETIME,'YYYY
- ------------------- -------------------
a 2010-01-04 08:00:00 2010-01-04 09:00:00
a 2010-01-04 09:30:00 2010-01-04 11:00:00
a 2010-01-04 13:00:00 2010-01-04 14:00:00
b 2010-01-04 08:00:00 2010-01-04 09:00:00
b 2010-01-04 08:30:00 2010-01-04 11:00:00
b 2010-01-04 13:00:00 2010-01-04 14:00:006 rows selected.SQL>
表 A 数据如下:
编号 开始时间 结束时间
a 2010-1-4 8:00:00 2010-1-4 9:00:00
a 2010-1-4 8:30:00 2010-1-4 10:00:00 应该得到如下结果:
a 2010-1-4 8:00:00 2010-1-4 10:00:00 所以1楼的好像还不是很对。。
create table tt as
select 'a' id,to_date('2010-1-4 8:00:00 ','yyyy-mm-dd hh24:mi:ss')starttime,to_date('2010-1-4 9:00:00 ','yyyy-mm-dd hh24:mi:ss')endtime from dual union all
select 'a' id,to_date('2010-1-4 8:30:00 ','yyyy-mm-dd hh24:mi:ss')starttime,to_date('2010-1-4 9:00:00 ','yyyy-mm-dd hh24:mi:ss')endtime from dual union all
select 'a' id,to_date('2010-1-4 9:30:00 ','yyyy-mm-dd hh24:mi:ss')starttime,to_date('2010-1-4 11:00:00 ','yyyy-mm-dd hh24:mi:ss')endtime from dual union all
select 'a' id,to_date('2010-1-4 10:00:00','yyyy-mm-dd hh24:mi:ss')starttime,to_date(' 2010-1-4 11:00:00','yyyy-mm-dd hh24:mi:ss')endtime from dual union all
select 'a' id,to_date('2010-1-4 13:00:00','yyyy-mm-dd hh24:mi:ss')starttime,to_date(' 2010-1-4 14:00:00','yyyy-mm-dd hh24:mi:ss')endtime from dual union all
select 'b' id,to_date('2010-1-4 8:00:00 ','yyyy-mm-dd hh24:mi:ss')starttime,to_date('2010-1-4 9:00:00 ','yyyy-mm-dd hh24:mi:ss')endtime from dual union all
select 'b' id,to_date('2010-1-4 8:30:00 ','yyyy-mm-dd hh24:mi:ss')starttime,to_date('2010-1-4 9:00:00 ','yyyy-mm-dd hh24:mi:ss')endtime from dual union all
select 'b' id,to_date('2010-1-4 8:30:00 ','yyyy-mm-dd hh24:mi:ss')starttime,to_date('2010-1-4 11:00:00 ','yyyy-mm-dd hh24:mi:ss')endtime from dual union all
select 'b' id,to_date('2010-1-4 10:00:00','yyyy-mm-dd hh24:mi:ss')starttime,to_date(' 2010-1-4 11:00:00','yyyy-mm-dd hh24:mi:ss')endtime from dual union all
select 'b' id,to_date('2010-1-4 13:00:00','yyyy-mm-dd hh24:mi:ss')starttime,to_date(' 2010-1-4 14:00:00','yyyy-mm-dd hh24:mi:ss')endtime from dual;
--查询
with t1 as(select tt.*,row_number()over(partition by id order by endtime,starttime)rn from tt)
select id,min(starttime),max(endtime) from t1 t
start with not exists(select 1 from t1 where id=t.id and rn=t.rn-1 and endtime>=t.starttime)
connect by id=prior id
and starttime<=prior endtime and rn=prior rn+1
group by id,rn-level;/*
ID MIN(STARTTIME) MAX(ENDTIME)
a 2010-1-4 8:00:00 2010-1-4 9:00:00
a 2010-1-4 9:30:00 2010-1-4 11:00:00
a 2010-1-4 13:00:00 2010-1-4 14:00:00
b 2010-1-4 8:00:00 2010-1-4 11:00:00
b 2010-1-4 13:00:00 2010-1-4 14:00:00
*/
排好序后,判断连续:两条记录间,下一条的starttime>=上一条的endtime,则为连续
将连续的记录通过connect by树形查询连起来
然后group by,将连续的记录合并到一条记录中
可以这么改下,看看group by前的rn和levelwith t1 as(select tt.*,row_number()over(partition by id order by endtime,starttime)rn from tt)
select id,starttime,endtime,rn,level from t1 t
start with not exists(select 1 from t1 where id=t.id and rn=t.rn-1 and endtime>=t.starttime)
connect by id=prior id
and starttime<=prior endtime and rn=prior rn+1
;
中的41楼的代码