没有测试环境,So,不知道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
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
解决方案 »
- 新接手无任何资料的库管系统(基于Oracle),资料更改请教
- 送分:求一简单SQL语句
- 关于Oracle Object for OLE
- 如何查看ORACLE中的TNS配置
- 在oracle里,where条件后这样写:列1=列2(+)有什么用啊?
- 求一sql语句
- 一个查询语句
- 在一个更新触发器中,如何知道那些列被更新了?在sqlserver中可以采用update(column),不知道在oracle中如何解决?
- 哪里有介绍数据库的设计方面的电子书。
- 有谁知道ORACLE的保存插入的数据临时表的位置.
- oracle 能不能像 sqlserver 那样定义临时表对象
- pl/sql 有无 bool-excption?a:b;这样的三元运算符?
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')