表TEST结构和内容如下:
日期 时间 编号 始发 到达
12 09:00 013 ZA TA
12 10:00 013 TA XA
12 09:30 020 ZA YA
12 10:10 027 ZA UA
12 11:20 034 SA RA
12 13:10 042 XA UA
12 14:20 042 ZA RA要求:
1、获取日期为12号的且始发的是ZA
2、如始发的不是ZA且与“其它”始发是ZA的编号相同,且到达站是“其它”始发站,时间也在“其它”后面
结果如下:
12 09:00 013 ZA TA
12 10:00 013 TA XA
12 09:30 020 ZA YA
12 10:10 027 ZA UA
12 14:20 042 ZA RA
日期 时间 编号 始发 到达
12 09:00 013 ZA TA
12 10:00 013 TA XA
12 09:30 020 ZA YA
12 10:10 027 ZA UA
12 11:20 034 SA RA
12 13:10 042 XA UA
12 14:20 042 ZA RA要求:
1、获取日期为12号的且始发的是ZA
2、如始发的不是ZA且与“其它”始发是ZA的编号相同,且到达站是“其它”始发站,时间也在“其它”后面
结果如下:
12 09:00 013 ZA TA
12 10:00 013 TA XA
12 09:30 020 ZA YA
12 10:10 027 ZA UA
12 14:20 042 ZA RA
解决方案 »
- 求asp连接oricle代码(不用输登陆帐号)
- 急啊,谁能帮帮我,XML解析问题.
- oracle卸载问题
- 大家感恩节快乐!~~顺便问个小问题 ORACLE 报的错:maximun number of sessions exceeded
- 插入数据报错,怎么解决?
- 100分在线等待oracle安装问题
- ORA-12560: TNS: 协议适配器错误
- controlfile文件丢失了,如何恢复呢?帮帮忙!!
- 安装oracle9i后iis的默认web服务被停止,请教高手
- oracle的rownumber查询 再添加一个条件查询
- oracle sql 语句 查询 从 n到m的记录
- 安装Oracle RAC时候的错误
where 日期='12' and 始发='ZA'
union all
select * from test a
where exists(select 1 from test b where b.日期='12' and b.始发='ZA' and a.编号=b.编号 and a.始发!=b.始发 and a.到达!=b.到达)
select * from test a where not exists(select 1 from test b where a.编号=b.编号 and a.时间>b.时间) and a.始发='ZA' and a.日期='12'
where 日期='12' and 始发='ZA'
union all
select * from test a
where exists(select 1 from test b where b.日期='12' and b.始发='ZA' and a.编号=b.编号 and a.始发!=b.始发 and a.到达!=b.到达 and a.时间>b.时间)呵呵,忘记了时间限定.
select c.*
from t2001 c
where c.flight_date = to_date('2010-10-15', 'yyyy-MM-dd')
and c.departure_airport = 'ZGSZ'
union all
select *
from t2001 a
where exists (select 1
from t2001 b
where b.flight_date = to_date('2010-10-15', 'yyyy-MM-dd')
and b.departure_airport = 'ZGSZ'
and a.flight_no = b.flight_no
and a.departure_airport != b.departure_airport
and a.arrival_airport != b.arrival_airport
and a.std > b.std)
select *
from t2001 a
where not exists (select 1
from t2001 b
where a.flight_no = b.flight_no
and a.std > b.std)
and a.departure_airport = 'ZGSZ'
and a.flight_date = to_date('2010-10-15', 'yyyy-MM-dd');
where 日期 = '12' --就算字符型
start with 始发 = 'ZA'
connect by 到达 = prior 始发 and 编号 = prior 编号 and 时间 > prior 时间
where 日期='12' and 始发='ZA'
union all
select * from test a
where exists(select 1 from test b where b.日期='12' and b.始发='ZA' and a.编号=b.编号 and a.始发!=b.始发 and a.到达!=b.到达 and a.时间>b.时间)
and a.日期='12'
SQL> with tb as
2 (
3 select '12' rq,'09:00' sj,'013' bh,'ZA' st,'TA' en from dual union all
4 select '12','10:00','013','TA','XA' from dual union all
5 select '12','09:30','020','ZA','YA' from dual union all
6 select '12','10:10','027','ZA','UA' from dual union all
7 select '12','11:20','034','SA','RA' from dual union all
8 select '12','13:10','042','XA','UA' from dual union all
9 select '12','14:20','042','ZA','RA' from dual
10 )
11 select * from tb where rq='12' and st='ZA'
12 union
13 select * from tb a
14 where exists(select 1 from tb b where a.bh=b.bh and a.sj>b.sj and b.rq='12' and b.st='ZA')
15 /RQ SJ BH ST EN
-- ----- --- -- --
12 09:00 013 ZA TA
12 09:30 020 ZA YA
12 10:00 013 TA XA
12 10:10 027 ZA UA
12 14:20 042 ZA RA