求教Sql写法！100分结账

Question

计算设备租用天数。有一批设备要计算一段时间内的租用天数，每个设备的起租/退租都有记录，记录在设备动态表里面，如下
      ID    CONTAINER_NO     MOVEMENT       TIME
       1       CN23456         起租        2006-03-05
       2       CN45234         退租        2006-03-06
       3       CN23456         退租        2006-03-05
       4       CN45234         起租        2006-03-14
       5       CN89709         退租        2006-03-16
       6       CN56789         起租        2006-02-11 
    要求计算2006-03-01到2006-03-31这段时间内的设备租用天数。分析问题有一下几种可能
     1)在这段时间内没有发生过退租的情况，但是起租日期不在本统计区，同样要计算统计区内使用天数
     2)在这段时间内，发生起租，但是没有退租，那么时间上限-起租日期则为实际使用天数
     3)在这段时间内，没有发生起租，但是发生了退租，那么退租日期-时间下限，为实际使用天数
     4)在这段时间内，同时发生了起租、退租两种情况，那么退租日起-起租日起，为实际使用天数
     5)在这段时间内，发生了多次起租、退租情况
结果要求SELECT出每个设备在统计时间内实际使用天数！求教SQL写法，请各位高手不吝赐教！多谢！

Answer 1

不知道DECODE能不能这么用：select
    a.CONTAINER_NO,
    sum(decode(TIME2>'2006-03-31',true,'2006-03-31',false,TIME2)-decode(TIME1<'2006-03-01',true,'2006-03-01',false,TIME1)) as NUM
from
    (select c.CONTAINER_NO,c.TIME as TIME1,(select min(TIME) from 表 where CONTAINER_NO=c.CONTAINER_NO and TIME>c.TIME and MOVEMENT='退租') as TIME2 from 表 c where c.MOVEMENT='起租' and c.TIME between '2006-03-01' and '2006-03-31'
     union 
     select d.CONTAINER_NO,(select max(TIME) from 表 where CONTAINER_NO=c.CONTAINER_NO and TIME<c.TIME and MOVEMENT='起租') as TIME1,d.TIME as TIME2 from 表 d where d.MOVEMENT='退租' and d.TIME between '2006-03-01' and '2006-03-31') a
group by
    a.CONTAINER_NO如果Oracle 9i及以上版本，可以用CASE WHEN ...select
    a.CONTAINER_NO,
    sum((CASE WHEN TIME2>'2006-03-31' THEN '2006-03-31' ELSE TIME2 END)-(CASE WHEN TIME1<'2006-03-01' THEN '2006-03-01' ELSE TIME1 END)) as NUM
from
    (select c.CONTAINER_NO,c.TIME as TIME1,(select min(TIME) from 表 where CONTAINER_NO=c.CONTAINER_NO and TIME>c.TIME and MOVEMENT='退租') as TIME2 from 表 c where c.MOVEMENT='起租' and c.TIME between '2006-03-01' and '2006-03-31'
     union 
     select d.CONTAINER_NO,(select max(TIME) from 表 where CONTAINER_NO=c.CONTAINER_NO and TIME<c.TIME and MOVEMENT='起租') as TIME1,d.TIME as TIME2 from 表 d where d.MOVEMENT='退租' and d.TIME between '2006-03-01' and '2006-03-31') a
group by
    a.CONTAINER_NO

Answer 2

select
    a.CONTAINER_NO,
    sum(decode(TIME2>'2006-03-31',true,'2006-03-31',false,TIME2)-decode(TIME1<'2006-03-01',true,'2006-03-01',false,TIME1)) as NUM
from
    (select c.CONTAINER_NO,c.TIME as TIME1,(select min(TIME) from 表 where CONTAINER_NO=c.CONTAINER_NO and TIME>c.TIME and MOVEMENT='退租') as TIME2 from 表 c where c.MOVEMENT='起租' and c.TIME between '2006-03-01' and '2006-03-31'
     union 
     select d.CONTAINER_NO,(select max(TIME) from 表 where CONTAINER_NO=c.CONTAINER_NO and TIME<c.TIME and MOVEMENT='起租') as TIME1,d.TIME as TIME2 from 表 d where d.MOVEMENT='退租' and d.TIME between '2006-03-01' and '2006-03-31') a
group by
    a.CONTAINER_NO
UNION
select 
    b.CONTAINER_NO,('2006-03-31'-'2006-03-01') 
from 
    表 b 
where 
    b.TIME=(select max(TIME) from 表 where CONTAINER_NO=b.CONTAINER_NO and TIME<'2006-03-01')
    and
    b.MOVEMENT='起租'
    and
    not exists(select 1 from 表 where CONTAINER_NO=b.CONTAINER_NO and TIME between '2006-03-01' and '2006-03-31')如果Oracle 9i及以上版本，可以用CASE WHEN ...select
    a.CONTAINER_NO,
    sum((CASE WHEN TIME2>'2006-03-31' THEN '2006-03-31' ELSE TIME2 END)-(CASE WHEN TIME1<'2006-03-01' THEN '2006-03-01' ELSE TIME1 END)) as NUM
from
    (select c.CONTAINER_NO,c.TIME as TIME1,(select min(TIME) from 表 where CONTAINER_NO=c.CONTAINER_NO and TIME>c.TIME and MOVEMENT='退租') as TIME2 from 表 c where c.MOVEMENT='起租' and c.TIME between '2006-03-01' and '2006-03-31'
     union 
     select d.CONTAINER_NO,(select max(TIME) from 表 where CONTAINER_NO=c.CONTAINER_NO and TIME<c.TIME and MOVEMENT='起租') as TIME1,d.TIME as TIME2 from 表 d where d.MOVEMENT='退租' and d.TIME between '2006-03-01' and '2006-03-31') a
group by
    a.CONTAINER_NO
UNION
select 
    b.CONTAINER_NO,('2006-03-31'-'2006-03-01') 
from 
    表 b 
where 
    b.TIME=(select max(TIME) from 表 where CONTAINER_NO=b.CONTAINER_NO and TIME<'2006-03-01')
    and
    b.MOVEMENT='起租'
    and
    not exists(select 1 from 表 where CONTAINER_NO=b.CONTAINER_NO and TIME between '2006-03-01' and '2006-03-31')

Answer 3

1,decode函数
2,case语句

Answer 4

我也是菜鸟，搞了将近两个半小时才写出下面的，希望可以帮到你。
CONTAINER_NO 对应  no
MOVEMENT     对应  1-起租，0-退租
TIME         对应  rent_time
参数  '&sday'  开始时间, '&eday' 结束时间
select c.no,
       sum(decode(c.movement,
                  1,
                  decode(nvl(c.end_rent, sysdate),
                         sysdate,
                         to_date('&eday', 'yyyy-mm-dd') - c.start_time,
                         end_rent - start_time),
                  end_rent - to_date('&sday', 'yyyy-mm-dd'))) actual_time
  from (select a.no,
               a.movement,
               a.rent_time start_time,
               (select min(rent_time)
                  from rent
                 where no = a.no
                   and rent_time >= a.rent_time
                   and rent_time between to_date('&sday', 'yyyy-mm-dd') and
                       to_date('&eday', 'yyyy-mm-dd')
                   and movement = 0) end_rent
          from rent a
         where a.movement = 1
           and a.rent_time between to_date('&sday', 'yyyy-mm-dd') and
               to_date('&eday', 'yyyy-mm-dd')
        union
        
        select a.no,
               a.movement,
               to_date(null) start_time,
               min(a.rent_time) end_rent
          from rent a,
               (select no, min(rent_time) t_time
                  from rent
                 where movement = 1
                   and rent_time between to_date('&sday', 'yyyy-mm-dd') and
                       to_date('&eday', 'yyyy-mm-dd')
                 group by no) b
         where a.movement = 0
           and a.no = b.no(+)
           and a.rent_time between to_date('&sday', 'yyyy-mm-dd') and
               to_date('&eday', 'yyyy-mm-dd')
         group by a.no, a.movement, b.t_time
        having min(a.rent_time) < nvl(b.t_time, to_date('9999-12-31', 'yyyy-mm-dd'))) c
 group by c.no

Answer 5

假设数据都是准确无误的，必然满足：
1)所有设备最多最近一次的退租记录没有（也就是说租了还没还）；
2)所有设备的各次起退租记录时间没有交叉再假设统计规则是起租日算使用用了，而退租日不算使用，即如果3.1日起租，3.2日退租算租了1天，并且每个设备没有当天租了并当天退租的，那么可以这样写
select CONTAINER_NO, 
       sum( least(time_z, to_date('2006-4-1','yyyy-mm-dd'))
           -greatest(time_q, to_date('2006-3-1','yyyy-mm-dd')) )
    from (select CONTAINER_NO, trunc(time) time_q, 
                 nvl( (select trunc(min(time)) from t t2
                           where t2.CONTAINER_NO=t1.CONTAINER_NO
                             and t2.MOVEMENT='退租'
                             and t2.time>t1.time)
                     ,to_date('2999-12-31','yyyy-mm-dd')) time_z
              from t t1
              where time<=to_date('2006-3-31','yyyy-mm-dd')) t0
    where t0.time_z>to_date('2006-3-1','yyyy-mm-dd')
    group by CONTAINER_NO不用union的写法：

Answer 6

漏了一个条件：
select CONTAINER_NO, 
       sum( least(time_z, to_date('2006-4-1','yyyy-mm-dd'))
           -greatest(time_q, to_date('2006-3-1','yyyy-mm-dd')) )
    from (select CONTAINER_NO, trunc(time) time_q, 
                 nvl( (select trunc(min(time)) from t t2
                           where t2.CONTAINER_NO=t1.CONTAINER_NO
                             and t2.MOVEMENT='退租'
                             and t2.time>t1.time)
                     ,to_date('2999-12-31','yyyy-mm-dd')) time_z
              from t t1
              where time<=to_date('2006-3-31','yyyy-mm-dd')
                and MOVEMENT='起租') t0
    where t0.time_z>to_date('2006-3-1','yyyy-mm-dd')
    group by CONTAINER_NO

Answer 7

楼上的SQL写一大堆，效率一定不高，不如用函数做