表里的记录是这样的: ID 地点 车牌号 经过时间
1 北一路 京A8888 2009-11-11 15:00:00
2 北二路 京A8888 2009-11-11 15:05:00
3 北一路 京A9999 2009-11-11 15:06:00
4 北二路 京A9999 2009-11-11 15:08:00最终的结果想是
车牌号 时间差
京A8888 5分钟
京A9999 2分钟 菜鸟上路,请多多指教,谢谢,呵呵
1 北一路 京A8888 2009-11-11 15:00:00
2 北二路 京A8888 2009-11-11 15:05:00
3 北一路 京A9999 2009-11-11 15:06:00
4 北二路 京A9999 2009-11-11 15:08:00最终的结果想是
车牌号 时间差
京A8888 5分钟
京A9999 2分钟 菜鸟上路,请多多指教,谢谢,呵呵
解决方案 »
- 用jdeveloper 编写sqlj时迭代器遇到的问题(oracle 10g)
- 一个sql的查询 求教怎么做
- win 2008 server 中 oracle 10g的双机是否可以做呢?
- sqlplus运行sql脚本文件的默认地址
- 如何实现以下查询结果
- 求助:一个单表查询SQL
- waiting for the oracle crsd and evmd to start
- 有谁说一下在ORACLE中用事务的一些体会?
- 请问:我想只取第100到300条的纪录,应该怎么做呢?
- 求教SQL查询语句(看下图表要求显示结果)
- 关于把java获取的当前时间插入oralc的问题
- 关于oracle 游标的问题[有点好奇]
像你现在示例,用MAX-MIN就可以
不过如果一条有多条纪录就不能这么做了,可以LAG这个分析函数来实现
from test3 a
group by a.car_num
2 select '1' id,'京A8888' card,'北一路' local,'2009-11-11 15:00:00' time from dual
3 union all select '2','京A8888','北二路','2009-11-11 15:05:00' from dual
4 union all select '3','京A9999','北一路','2009-11-11 15:06:00' from dual
5 union all select '4','京A9999','北二路','2009-11-11 15:08:00' from dual
6 )
7 select card,(max(to_date(time,'yyyy-mm-dd hh24:mi:ss'))- min(to_date(time,'yyyy-mm-dd hh24:mi:ss')))*60*24 || 'minite' inter from tt group by card;CARD INTER
------- ----------------------------------------------
京A8888 5minite
京A9999 2minite
from(
select t.*,
lag(地点)over(partition by 车牌号 order by 经过时间)lgstreet,
lag(经过时间)over(partition by 车牌号 order by 经过时间)lgtime
from table1 t)
where 地点='北二路' and lgstreet='北一路'
select 1 id,'北一路' address,'京A8888' carno,to_date('2009-11-11 15:00:00','yyyy-mm-dd hh24:mi:ss') passtime from dual
union all
select 2 id,'北二路' address,'京A8888' carno,to_date('2009-11-11 15:05:00','yyyy-mm-dd hh24:mi:ss') passtime from dual
union all
select 3 id,'北一路' address,'京A9999' carno,to_date('2009-11-11 15:06:00','yyyy-mm-dd hh24:mi:ss') passtime from dual
union all
select 4 id,'北二路' address,'京A9999' carno,to_date('2009-11-11 15:08:00','yyyy-mm-dd hh24:mi:ss') passtime from dual
)
select carno,timecha from (
select carno,timecha,row_number() over(partition by carno order by carno) rn from (
select carno,round(to_number(passtime-lag(passtime) over(order by carno))*24*60)||'分钟' timecha from temp
)
) where rn =2;
SQL> select id,loc,num,to_char(pass,'YYYY-MM-DD HH24:MI:SS') from t3; ID LOC NUM TO_CHAR(PASS,'YYYY-
---------- -------------------- ---------- -------------------
1 fisrt road 111 2009-11-11 15:00:00
2 second road 111 2009-11-11 15:30:00
3 second road 222 2009-11-10 11:30:00
4 first road 222 2009-11-10 10:30:00
5 first road 111 2009-11-09 12:30:00
6 second road 111 2009-11-09 12:40:006 rows selected.
SQL> select id,num,(pass-lgtime)*24*60 inter
2 from(
3 select t.*,
4 lag(loc) over (partition by num order by pass) lgstreet,
5 lag(pass) over (partition by num order by pass) lgtime
6 from t3 t)
7 where loc='second road' and lgstreet='first road'; ID NUM INTER
---------- ---------- ----------
6 111 10
2 111 30
3 222 60/*当表中记录没有重复的时候 也就是说没有记录同一车牌两次经过这条线路的时候
可以简单点 用下面的句子就可以了 否则就会出现错误 如下所示
系统会用 2 second road 111 2009-11-11 15:30:00
去减 5 first road 111 2009-11-09 12:30:00
当然就会出现错误了*/
SQL> select num,(max(pass) - min(pass))*60*24 || 'minite' inter from t3 group by
num; NUM INTER
---------- ----------------------------------------------
222 60minite
111 3060minite
SQL> create table t3(id number,loc varchar2(20),num number,pass date);Table created.其中loc是所经过地点 num是车牌号 pass是经过该地点时间
2 from(
3 select t.*,
4 lag(loc) over (partition by num order by pass) lgstreet,
5 lag(pass) over (partition by num order by pass) lgtime
6 from t3 t)
7 where loc='second road' and lgstreet='first road';
(round((to_number(max(v_time)-min(v_time))*60*24))||'分') "时间差"
from compare
group by car_no
如果地点变为三个或更多个
1 fisrt road 111 2009-11-11 15:00:00
2 second road 111 2009-11-11 15:30:00
3 second road 222 2009-11-10 11:30:00
4 first road 222 2009-11-10 10:30:00
5 first road 111 2009-11-09 12:30:00
6 second road 111 2009-11-09 12:40:00
7 third road 222 2009-11-09 12:00:00
8 fourth road 222 2009-11-09 12:10:00
9 fifth road 222 2009-11-09 12:30:00
10 fifth road 555 2009-11-09 12:30:00
------------------------
如上所述,num为222的车经过了第1,2,3,4,5 共5个地点, num这111的;一经地了第1,2共两个点,
求同时经过第1,2点的车是可以了,但是如果求经过第1,3两点的车呢?或求经过第1,5点的车呢?