mo_number item_sn key_part_no LOADKPPOINTSN loadtime
1 120601 BPI00101255571 5271610001 31F-34 2010-12-7 10:04:47 1
2 120601 BPI00101274117 5271610001 31F-34 2010-12-7 10:23:56 2
3 120601 BPI00101276971 5271511201 31F-33 2010-12-7 10:22:01 3
4 120601 BPI00101291994 5271671001 32F-01 2010-12-7 10:24:58 4
5 120601 BPI00101291996 5271671001 32F-01 2010-12-7 9:34:49 5
6 120601 BPI00101297446 5271511201 31F-35 2010-12-7 10:22:57 6
7 120601 BPI00101298374 5272309001 33R-01 2010-12-7 10:24:30 7就是当一条记录与下一条记录的key_part_no与LOADKPPOINTSN都相同时,且前条记录的loadtime小于下一条记录的loadtime时,
写一SQL语句查询时前条记录的loadtime为下一条记录的loadtime
谢谢
怎么写SQL呢
解决方案 »
- Oracle where rownum语句与Order by的优先级
- 同一语句在9i,和10G下查询结果不同,难道 1+1=2 ,3-1<>2么?
- 备份除某表以外的其它表的方法?
- PL SQL 的SQL窗口无法执行存储过程
- JOB的问题
- 关于SQL语句的问题
- truncate table 出错是怎么回事?
- 小弟想学习Oracle,请各位高手大哥介绍1本初级的经典著作(我怕买错书,还有就是最怕买到垃圾书呀),我要买书,不用电子版的了!
- Oracle中的数据怎么转换成XML文档?
- oracle8i中Oracle EnterPrise Manager中备份数据库时提示设置首选身份证明??
- “数据块损坏”预警日志错误:
- jdbc连接数据库出错,请教高手
from tablename
from tablename
from tablename
select 120601 mo_number,'BPI00101255571' item_sn,'31F-34 ' key_part_no,
'5271610001' LOADKPPOINTSN, '20101207 10:04:47' loadtime from dual union all
select 120601,'BPI00101274117','5271610001','31F-34','20101207 10:23:56' from dual union all
select 120601,'BPI00101276971','5271511201','31F-33','20101207 10:22:01' from dual union all
select 120601,'BPI00101291994','5271671001','32F-01','20101207 10:24:58' from dual union all
select 120601,'BPI00101291996','5271671001','32F-01','20101207 9:34:49' from dual union all
select 120601,'BPI00101297446','5271511201','31F-35','20101207 10:22:57' from dual union all
select 120601,'BPI00101298374','5272309001','33R-01','20101207 10:24:30' from dual)
--以上为提供数据的语句
select mo_number,item_sn,key_part_no,LOADKPPOINTSN,
(case when loadtime<nextloadtime then nextloadtime else loadtime end) loadtime
--如果loadtime<nextloadtime 则显示nextloadtime否则显示loadtime
from (
select mo_number,item_sn,key_part_no,LOADKPPOINTSN,loadtime,
lead(loadtime) over(partition by mo_number order by key_part_no,LOADKPPOINTSN) nextloadtime
--利用分析函数找到下一个loadtime
from tb
) MO_NUMBER ITEM_SN KEY_PART_N LOADKPPOIN LOADTIME
---------- -------------- ---------- ---------- -----------------
120601 BPI00101255571 31F-34 5271610001 20101207 10:22:01
120601 BPI00101276971 5271511201 31F-33 20101207 10:22:57
120601 BPI00101297446 5271511201 31F-35 20101207 10:23:56
120601 BPI00101274117 5271610001 31F-34 20101207 9:34:49
120601 BPI00101291996 5271671001 32F-01 20101207 9:34:49
120601 BPI00101291994 5271671001 32F-01 20101207 10:24:58
120601 BPI00101298374 5272309001 33R-01 20101207 10:24:30已选择7行。
select 120601 mo_number,'BPI00101255571' item_sn,'31F-34 ' key_part_no,
'5271610001' LOADKPPOINTSN, '20101207 10:04:47' loadtime from dual union all
select 120601,'BPI00101274117','5271610001','31F-34','20101207 10:23:56' from dual union all
select 120601,'BPI00101276971','5271511201','31F-33','20101207 10:22:01' from dual union all
select 120601,'BPI00101291994','5271671001','32F-01','20101207 10:24:58' from dual union all
select 120601,'BPI00101291996','5271671001','32F-01','20101207 9:34:49' from dual union all
select 120601,'BPI00101297446','5271511201','31F-35','20101207 10:22:57' from dual union all
select 120601,'BPI00101298374','5272309001','33R-01','20101207 10:24:30' from dual)
--以上为提供数据的语句
select mo_number,item_sn,key_part_no,LOADKPPOINTSN,
(case when loadtime<nextloadtime then nextloadtime else loadtime end) loadtime
--如果loadtime<nextloadtime 则显示nextloadtime否则显示loadtime
from (
select mo_number,item_sn,key_part_no,LOADKPPOINTSN,loadtime,
lead(loadtime) over(partition by key_part_no,LOADKPPOINTSN order by loadtime ) nextloadtime
--利用分析函数找到下一个loadtime from tb
) MO_NUMBER ITEM_SN KEY_PART_N LOADKPPOIN LOADTIME
---------- -------------- ---------- ---------- -----------------
120601 BPI00101255571 31F-34 5271610001 20101207 10:04:47
120601 BPI00101276971 5271511201 31F-33 20101207 10:22:01
120601 BPI00101297446 5271511201 31F-35 20101207 10:22:57
120601 BPI00101274117 5271610001 31F-34 20101207 10:23:56
120601 BPI00101291994 5271671001 32F-01 20101207 9:34:49
120601 BPI00101291996 5271671001 32F-01 20101207 9:34:49
120601 BPI00101298374 5272309001 33R-01 20101207 10:24:30
2 select 120601 mo_number,'BPI00101255571' item_sn,'31F-34 ' key_part_no,
3 '5271610001' LOADKPPOINTSN, '20101207 10:04:47' loadtime from dual union all
4 select 120601,'BPI00101274117','5271610001','31F-34','20101207 10:23:56' from dual union all
5 select 120601,'BPI00101276971','5271511201','31F-33','20101207 10:22:01' from dual union all
6 select 120601,'BPI00101291994','5271671001','32F-01','20101207 10:24:58' from dual union all
7 select 120601,'BPI00101291996','5271671001','32F-01','20101207 9:34:49' from dual union all
8 select 120601,'BPI00101297446','5271511201','31F-35','20101207 10:22:57' from dual union all
9 select 120601,'BPI00101298374','5272309001','33R-01','20101207 10:24:30' from dual)
10 select mo_number, item_sn, key_part_no, LOADKPPOINTSN ,lead(loadtime,1,loadtime)over(partition by key_part_no,LOADKPPOINTSN order by loadtime)
11 from tb;
MO_NUMBER ITEM_SN KEY_PART_NO LOADKPPOINTSN LEAD(LOADTIME,1,LOADTIME)OVER(
---------- -------------- ----------- ------------- ------------------------------
120601 BPI00101255571 31F-34 5271610001 20101207 10:04:47
120601 BPI00101276971 5271511201 31F-33 20101207 10:22:01
120601 BPI00101297446 5271511201 31F-35 20101207 10:22:57
120601 BPI00101274117 5271610001 31F-34 20101207 10:23:56
120601 BPI00101291994 5271671001 32F-01 20101207 9:34:49
120601 BPI00101291996 5271671001 32F-01 20101207 9:34:49
120601 BPI00101298374 5272309001 33R-01 20101207 10:24:30
7 rows selected
SQL>
你们两个速度好快哦
SQL> with tb as(
2 select 120601 mo_number ,'BPI00101255571' item_sn, 5271610001 key_part_no,'31F-34' LOADKPPOINTSN ,'2010-12-7 10:04:47' loadtime from dual union all
3 select 120601 ,'BPI00101274117', 5271610001, '31F-34', '2010-12-7 10:23:56' from dual union all
4 select 120601 ,'BPI00101276971', 5271511201, '31F-33', '2010-12-7 10:22:01' from dual union all
5 select 120601 ,'BPI00101291994', 5271671001, '32F-01', '2010-12-7 10:24:58' from dual union all
6 select 120601 ,'BPI00101291996', 5271671001, '32F-01', '2010-12-7 9:34:49' from dual union all
7 select 120601 ,'BPI00101297446', 5271511201, '31F-35', '2010-12-7 10:22:57' from dual union all
8 select 120601 ,'BPI00101298374', 5272309001, '33R-01', '2010-12-7 10:24:30' from dual)
9 select mo_number,item_sn,key_part_no,LOADKPPOINTSN,loadtime,
10 lead(loadtime,1,loadtime) over(partition by key_part_no,LOADKPPOINTSN order by loadtime)
11 from tb
12 /
MO_NUMBER ITEM_SN KEY_PART_NO LOADKPPOINTSN LOADTIME LEAD(LOADTIME,1,LOADTIME)OVER(
---------- -------------- ----------- ------------- ------------------ ------------------------------
120601 BPI00101276971 5271511201 31F-33 2010-12-7 10:22:01 2010-12-7 10:22:01
120601 BPI00101297446 5271511201 31F-35 2010-12-7 10:22:57 2010-12-7 10:22:57
120601 BPI00101255571 5271610001 31F-34 2010-12-7 10:04:47 2010-12-7 10:23:56
120601 BPI00101274117 5271610001 31F-34 2010-12-7 10:23:56 2010-12-7 10:23:56
120601 BPI00101291994 5271671001 32F-01 2010-12-7 10:24:58 2010-12-7 9:34:49
120601 BPI00101291996 5271671001 32F-01 2010-12-7 9:34:49 2010-12-7 9:34:49
120601 BPI00101298374 5272309001 33R-01 2010-12-7 10:24:30 2010-12-7 10:24:30
7 rows selected