select CAR_ID,case WHEN max(t2.can_use)/t2.seq=0 then CAR_USE_MODE else '空闲' end CAR_USE_MODE, nvl(lag(max(t2.car_date))over(order by min(t2.car_date)),min(t2.car_date)) startDate, max(t2.car_date) EndDate,case when max(t2.can_use)/t2.seq=0 then USE_NO ELSE '' END USE_NO 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_{0} where car_id={1} order by car_state_{0}.car_date) tt) t1) t2 group by CAR_ID,t2.seq,USE_NO,CAR_USE_MODE order by min(t2.car_date)
测试数据:create table tab_test 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,'' CAR_USE_MODE,'' USE_NO 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,'租赁','HT12011600402' 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,'租赁','HT12011600402' 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,'租赁','HT12011600402' 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,'租赁','HT12011600402 ' 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,'租赁','HT12011600402' 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,'租赁','HT12011600402' 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,'租赁','HT12011600402' from dual;执行代码:select b.can_use, min(c_date), max(c_date), nvl(car_use_mode, '无'), nvl(use_no, '无'), b.des from (select a.can_use, a.c_date, a.des, car_use_mode, use_no, sum(flag) over(order by rowid) rn from (select t.*,decode(lag(can_use) over(order by rowid),can_use,0,1) flag from tab_test t) a) b group by b.can_use, b.rn, car_use_mode, use_no, b.des order by can_use desc, min(c_date);执行结果: CAN_USE MIN(C_DATE) MAX(C_DATE) NVL(CAR_USE_MODE,'无') NVL(USE_NO,'无') DES 1 1 2012-01-14 0:10:00 2012-01-14 1:00:00 无 无 繁忙 2 1 2012-01-14 2:10:00 2012-01-14 2:10:00 保养 HT12011600402 繁忙 3 0 2012-01-14 1:10:00 2012-01-14 1:30:00 租赁 HT12011600402 空闲 4 0 2012-01-14 1:40:00 2012-01-14 2:00:00 保养 HT12011600402 空闲PS:这100分真是体力活,如果楼主提供CREATE,再给出完整的想要的结果的话,回答的人会很多很快,这种提问方式比较抽象。
nvl(lag(max(t2.car_date))over(order by min(t2.car_date)),min(t2.car_date)) startDate,
max(t2.car_date) EndDate,case when max(t2.can_use)/t2.seq=0 then USE_NO ELSE '' END USE_NO 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_{0} where car_id={1} order by car_state_{0}.car_date) tt) t1) t2
group by CAR_ID,t2.seq,USE_NO,CAR_USE_MODE order by min(t2.car_date)
select 1 as can_use, to_date('2012-01-14 0:10:00', 'yyyy-MM-dd HH24:mi:ss') as c_date , '繁忙' as des,''
CAR_USE_MODE,'' USE_NO 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,'租赁','HT12011600402' 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,'租赁','HT12011600402' 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,'租赁','HT12011600402' 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,'租赁','HT12011600402
' 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,'租赁','HT12011600402' 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,'租赁','HT12011600402' 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,'租赁','HT12011600402' from dual;执行代码:select b.can_use,
min(c_date),
max(c_date),
nvl(car_use_mode, '无'),
nvl(use_no, '无'),
b.des
from (select a.can_use,
a.c_date,
a.des,
car_use_mode,
use_no,
sum(flag) over(order by rowid) rn
from (select t.*,decode(lag(can_use) over(order by rowid),can_use,0,1) flag
from tab_test t) a) b
group by b.can_use, b.rn, car_use_mode, use_no, b.des
order by can_use desc, min(c_date);执行结果: CAN_USE MIN(C_DATE) MAX(C_DATE) NVL(CAR_USE_MODE,'无') NVL(USE_NO,'无') DES
1 1 2012-01-14 0:10:00 2012-01-14 1:00:00 无 无 繁忙
2 1 2012-01-14 2:10:00 2012-01-14 2:10:00 保养 HT12011600402 繁忙
3 0 2012-01-14 1:10:00 2012-01-14 1:30:00 租赁 HT12011600402 空闲
4 0 2012-01-14 1:40:00 2012-01-14 2:00:00 保养 HT12011600402 空闲PS:这100分真是体力活,如果楼主提供CREATE,再给出完整的想要的结果的话,回答的人会很多很快,这种提问方式比较抽象。