求一条比较复杂的sql语句！

Question

oracle数据库表机构如下：can_use        c_date            des
1        2012-1-14 0:10:00      繁忙      
1        2012-1-14 0:20:00      繁忙
1        2012-1-14 0:30:00      繁忙
1        2012-1-14 0:40:00      繁忙
1        2012-1-14 0:50:00      繁忙
1        2012-1-14 1:00:00      繁忙
0        2012-1-14 1:10:00      空闲
0        2012-1-14 1:20:00      空闲
0        2012-1-14 1:30:00      空闲
0        2012-1-14 1:40:00      空闲
0        2012-1-14 1:50:00      空闲
0        2012-1-14 2:00:00      空闲需要分段找出can_use为0的时间“段”。数据可以有十万条，can_use中的0和1可能交替出现N遍，结果应该得出N条记录，
如上活得的结果是
can_use     star                    end                 des
0         2012-1-14 1:10:00       2012-1-14 2:00:00    空闲当然，能将1、0按照时间先后顺序交替列出来更好了。。谢谢啦。

Answer 1

0或者1都可以。。结果为can_use star end des
1 2012-1-14 0:10:00 2012-1-14 1:00:00 繁忙
0 2012-1-14 1:10:00 2012-1-14 2:00:00 空闲
1……
0……
1……
0……

Answer 2

当然，加条件过滤一下也可以，找出can_use为0或者1的都可以

Answer 3

试一下，没测试。
select can_use,min(c_date) as start, max(c_date) as end ,des from(
  select * from tableName where can_use = 0
)
group by can_use,des

Answer 4

直接报错了，“执行失败:ORA-00923: 未找到要求的 FROM 关键字”“select * from tableName where can_use = 0”修改tabName后，这条可以执行。。

Answer 5

--can_use c_date des
with t as (
select 1 as can_use, '2012-1-14 0:10:00' as c_date, '繁忙' as des from dual
union all
select 1, '2012-1-14 0:20:00', '繁忙' from dual
union all
select 1, '2012-1-14 0:30:00', '繁忙' from dual
union all
select 1, '2012-1-14 0:40:00', '繁忙' from dual
union all
select 1, '2012-1-14 0:50:00', '繁忙' from dual
union all
select 1, '2012-1-14 1:00:00', '繁忙' from dual
union all
select 0, '2012-1-14 1:10:00', '空闲' from dual
union all
select 0, '2012-1-14 1:20:00', '空闲' from dual
union all
select 0, '2012-1-14 1:30:00', '空闲' from dual
union all
select 0, '2012-1-14 1:40:00', '空闲' from dual
union all
select 0, '2012-1-14 1:50:00', '空闲' from dual
union all
select 0, '2012-1-14 2:00:00', '空闲' from dual
)
select can_use, min(c_date) as fstart, max(c_date) as fend ,des from (
  select can_use,to_date(c_date,'yyyy-mm-dd hh24:mi:ss') as c_date,des from t where can_use = 0
) group by can_use,desCAN_USE                FSTART                    FEND                      DES    
---------------------- ------------------------- ------------------------- ------ 
0                      2012-01-14 01:10:00       2012-01-14 02:00:00       空闲  

Answer 6

with t as (
select 1 as can_use, to_date('2012-01-14 0:10:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 1 as can_use, to_date('2012-01-14 0:20:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 1 as can_use, to_date('2012-01-14 0:30:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 1 as can_use, to_date('2012-01-14 0:40:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 1 as can_use, to_date('2012-01-14 0:50:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 1 as can_use, to_date('2012-01-14 1:00:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual union all
select 0 as can_use, to_date('2012-01-14 1:10:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 0 as can_use, to_date('2012-01-14 1:20:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 0 as can_use, to_date('2012-01-14 1:30:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 0 as can_use, to_date('2012-01-14 1:40:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 0 as can_use, to_date('2012-01-14 1:50:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 0 as can_use, to_date('2012-01-14 2:00:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '空闲' as des from dual union all
select 1 as can_use, to_date('2012-01-14 2:10:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des from dual
)
select mod(t2.seq,2) can_use,min(t2.c_date),max(t2.c_date),decode(mod(t2.seq,2),0,'空闲','繁忙') des from (
select sum(t1.rn) over(order by t1.c_date) seq,t1.* from (
select decode(lag(tt.can_use)over(order by tt.c_date),tt.can_use,0,null,1,1) as rn,tt.* from (select * from t order by t.c_date) tt) t1) t2
group by t2.seq;
----------------------------------------------1 2012-01-14 00:10:00 2012-01-14 01:00:00 繁忙
0 2012-01-14 01:10:00 2012-01-14 02:00:00 空闲
1 2012-01-14 02:10:00 2012-01-14 02:10:00 繁忙

Answer 7

执行失败:ORA-00904: "T"."CRE_DATE": 标识符无效

Answer 8

格式差不多正确了，但是结果好像不对
结果如下：CAN_USE MIN(T2.C_DATE) MAX(T2.C_DATE) DES
1 2012-1-14 0:10:00 2012-1-19 14:40:00 繁忙
1 2012-6-25 4:00:00 2012-6-25 4:00:00 繁忙
1 2012-6-25 10:00:00 2012-6-25 10:00:00 繁忙
1 2012-6-25 19:00:00 2012-6-25 19:00:00 繁忙
1 2012-6-25 19:20:00 2012-6-25 19:20:00 繁忙
1 2012-6-25 19:50:00 2012-6-25 19:50:00 繁忙
1 2012-6-25 22:00:00 2012-6-25 22:00:00 繁忙
1 2012-6-25 23:40:00 2012-6-25 23:40:00 繁忙
第一：繁忙和空闲应该是交替的，如果两个“繁忙”或者“空闲”在一起，应该改变起止时间的跨度。
第二：“繁忙”或“空闲”都是时间段开始时间和结束时间一般不一样。。

Answer 9

with t as (.....)这段是准备测试数据.....你可以不看....select mod(t2.seq,2) can_use,min(t2.c_date),max(t2.c_date),decode(mod(t2.seq,2),0,'空闲','繁忙') des from (
select sum(t1.rn) over(order by t1.c_date) seq,t1.* from (
select decode(lag(tt.can_use)over(order by tt.c_date),tt.can_use,0,null,1,1) as rn,tt.* from (select * from t order by t.c_date) tt) t1) t2
group by t2.seq;这段是语句....
里面的 select * from t order by t.c_date
这个改成你的表...
我是按c_date排序的...应该是符合你的要求...

Answer 10

我改了下，不过结果排序好像还是有点问题。修改后：select mod(t2.seq,2) can_use,min(t2.car_date),max(t2.car_date),decode(mod(t2.seq,2),0,'繁忙','空闲') des from (
select sum(t1.rn) over(order by t1.car_date) seq,t1.* from (
select decode(lag(tt.can_use)over(order by tt.car_date),tt.can_use,0,null,1,1) as rn,tt.* from (select * from car_state_96 where car_id=139 order by car_state_96.car_date) tt) t1) t2
group by t2.seq;
结果如下：
CAN_USE MIN(T2.CAR_DATE) MAX(T2.CAR_DATE) DES
1 2012-1-14 0:10:00 2012-2-5 13:20:00 空闲
0 2012-3-23 19:00:00 2012-3-25 19:00:00 繁忙
1 2012-4-14 11:40:00 2012-4-16 9:50:00 空闲
1 2012-4-16 10:40:00 2012-4-16 11:10:00 空闲
0 2012-2-5 13:30:00 2012-3-6 13:20:00 繁忙
0 2012-4-16 11:20:00 2012-4-20 繁忙
0 2012-5-24 18:00:00 2012-5-27 19:30:00 繁忙
1 2012-5-27 19:40:00 2012-9-1 空闲
0 2012-3-9 12:30:00 2012-3-11 13:10:00 繁忙
1 2012-3-11 13:20:00 2012-3-23 18:50:00 空闲
0 2012-4-1 19:00:00 2012-4-6 15:30:00 繁忙
1 2012-5-2 15:30:00 2012-5-8 23:50:00 空闲
1 2012-3-6 13:30:00 2012-3-9 12:20:00 空闲
1 2012-3-25 19:10:00 2012-4-1 18:50:00 空闲
0 2012-5-9 2012-5-14 8:00:00 繁忙
1 2012-4-6 15:40:00 2012-4-10 11:20:00 空闲
如果按照第二列排序就更直观了

Answer 11

搞定了，尝试着改了一下：select mod(t2.seq,2) can_use,min(t2.car_date),max(t2.car_date),decode(mod(t2.seq,2),0,'繁忙','空闲') des from (
select sum(t1.rn) over(order by t1.car_date) seq,t1.* from (
select decode(lag(tt.can_use)over(order by tt.car_date),tt.can_use,0,null,1,1) as rn,tt.* from (select * from car_state_96 where car_id=139 order by car_state_96.car_date) tt) t1) t2
group by t2.seq order by min(t2.car_date);结果如下：
CAN_USE MIN(T2.CAR_DATE) MAX(T2.CAR_DATE) DES
1 2012-5-27 19:40:00 2012-9-1 空闲
1 2012-5-14 8:10:00 2012-5-24 17:50:00 空闲
1 2012-3-11 13:20:00 2012-3-23 18:50:00 空闲
1 2012-5-2 15:30:00 2012-5-8 23:50:00 空闲
1 2012-4-16 10:40:00 2012-4-16 11:10:00 空闲
1 2012-4-20 0:10:00 2012-4-29 15:50:00 空闲
1 2012-3-6 13:30:00 2012-3-9 12:20:00 空闲
1 2012-4-14 11:40:00 2012-4-16 9:50:00 空闲
1 2012-4-6 15:40:00 2012-4-10 11:20:00 空闲
1 2012-3-25 19:10:00 2012-4-1 18:50:00 空闲
1 2012-1-14 0:10:00 2012-2-5 13:20:00 空闲
0 2012-4-29 16:00:00 2012-5-2 15:20:00 繁忙
0 2012-4-1 19:00:00 2012-4-6 15:30:00 繁忙
0 2012-5-9 2012-5-14 8:00:00 繁忙
0 2012-3-23 19:00:00 2012-3-25 19:00:00 繁忙
0 2012-4-10 11:30:00 2012-4-14 11:30:00 繁忙
0 2012-3-9 12:30:00 2012-3-11 13:10:00 繁忙
0 2012-4-16 11:20:00 2012-4-20 繁忙
0 2012-2-5 13:30:00 2012-3-6 13:20:00 繁忙
0 2012-4-16 10:00:00 2012-4-16 10:30:00 繁忙
0 2012-5-24 18:00:00 2012-5-27 19:30:00 繁忙
最后还有一个问题。你sql咋搞得这么“BT”了？？？佩服啊！！！！

Answer 12

CAN_USE MIN(T2.CAR_DATE) MAX(T2.CAR_DATE) DES
1 2012-1-14 0:10:00 2012-2-5 13:20:00 空闲
0 2012-2-5 13:30:00 2012-3-6 13:20:00 繁忙
1 2012-3-6 13:30:00 2012-3-9 12:20:00 空闲
0 2012-3-9 12:30:00 2012-3-11 13:10:00 繁忙
1 2012-3-11 13:20:00 2012-3-23 18:50:00 空闲
0 2012-3-23 19:00:00 2012-3-25 19:00:00 繁忙
1 2012-3-25 19:10:00 2012-4-1 18:50:00 空闲
0 2012-4-1 19:00:00 2012-4-6 15:30:00 繁忙
1 2012-4-6 15:40:00 2012-4-10 11:20:00 空闲
0 2012-4-10 11:30:00 2012-4-14 11:30:00 繁忙
1 2012-4-14 11:40:00 2012-4-16 9:50:00 空闲
0 2012-4-16 10:00:00 2012-4-16 10:30:00 繁忙
1 2012-4-16 10:40:00 2012-4-16 11:10:00 空闲
0 2012-4-16 11:20:00 2012-4-20 繁忙
1 2012-4-20 0:10:00 2012-4-29 15:50:00 空闲
0 2012-4-29 16:00:00 2012-5-2 15:20:00 繁忙
1 2012-5-2 15:30:00 2012-5-8 23:50:00 空闲
0 2012-5-9 2012-5-14 8:00:00 繁忙
1 2012-5-14 8:10:00 2012-5-24 17:50:00 空闲
0 2012-5-24 18:00:00 2012-5-27 19:30:00 繁忙
1 2012-5-27 19:40:00 2012-9-1 空闲

Answer 13

这个SQL还好吧..没多BT啊...逻辑还是比较清晰的..就是写得乱一点吧....

Answer 14

小白 路过，学习一下
lz 发现这条sql 还有点小问题哦：
不管第一条数据是否为繁忙。统计sql 都统计为繁忙。按照 #7楼的 方法测试。不过把测试数据的 中的 can_use 字段 0 改为 1, 1 改为 0(即繁忙改为空闲，空闲改为繁忙)，在执行sql 会发现统计结果第一条为繁忙。

Answer 15

稍微修改一下就行了...
select decode(max(t2.can_use)/t2.seq,0,0,1) can_use,min(t2.c_date),max(t2.c_date),decode(max(t2.can_use)/t2.seq,0,'空闲','繁忙') from (
select sum(t1.rn) over(order by t1.c_date) seq,t1.* from (
select decode(lag(tt.can_use)over(order by tt.c_date),tt.can_use,0,null,1,1) as rn,tt.* from (select * from t order by t.c_date) tt) t1) t2 
group  by t2.seq order by min(t2.c_date);

Answer 16

 talentwing 的这个sql 的想法还是蛮不错的

Answer 17

这个不复杂..稍微改改就好了....
select decode(max(t2.can_use)/t2.seq,0,0,1) can_use,nvl(lag(max(t2.c_date))over(order by min(t2.c_date)),min(t2.c_date)),max(t2.c_date),decode(max(t2.can_use)/t2.seq,0,'空闲','繁忙') from (
select sum(t1.rn) over(order by t1.c_date) seq,t1.* from (
select decode(lag(tt.can_use)over(order by tt.c_date),tt.can_use,0,null,1,1) as rn,tt.* from (select * from t order by t.c_date) tt) t1) t2  
group by t2.seq order by min(t2.c_date);
其实还是那几个函数......

Answer 18

数据如下：
CAN_USE CAR_USE_MODE USE_NO
1
1
1
1
1
0 租赁 HT12011600402
0 租赁 HT12011600402
0 租赁 HT12011600402
0 租赁 HT12011600402
0 租赁 HT12011600402
0 租赁 HT12011600402
0 租赁 HT12011600402
0 保养 BY12011600402
0 保养 BY12011600402
0 保养 BY12011600402
0 保养 BY12011600402需要提取出车辆使用方式和记录单号
结果改变了，显示的方式应该是按照CAR_USE_MODE或者USE_NO“分组”，显示结果如下。
1 2012-5-14 8:10:00 2012-5-24 17:50:00 空闲    无         无
0 2012-5-24 18:00:00 2012-5-27 19:30:00 繁忙    租赁       HT12011600402
0 2012-5-27 19:40:00 2012-9-1 繁忙            保养       BY12011600402