ORACLE查询离多个时间最近的数据

qqww09633 2020-05-25 11:15:06
各位大哥,我有一张表格,想查询离条件中时间最近的数据,查询多笔后一起输出。请问怎样写??
举例如下:
表格(TEST)内容:

ID TIMEER
H001 2020-05-24 02:39:30
H001 2020-05-24 05:46:57
H001 2020-05-24 20:12:40
H002 2020-05-24 01:58:57
H002 2020-05-24 04:54:06
H002 2020-05-24 06:22:33
H002 2020-05-24 20:45:15
H003 2020-05-24 00:52:47
H003 2020-05-24 13:31:59
H005 2020-05-24 06:10:15
H006 2020-05-24 02:11:12
H006 2020-05-24 07:11:16
H006 2020-05-24 10:26:08
H006 2020-05-24 21:54:36
H006 2020-05-24 22:26:29

我想查询以下几个条件(条件不定),离TIMEER时间最近的数据:

ID TIMEER
H001 2020-05-24 05:00:00
H002 2020-05-24 21:00:00
H006 2020-05-24 22:00:00

想要的结果如下:(离条件中时间最近)

ID TIMEER
H001 2020-05-24 05:46:57
H002 2020-05-24 20:45:15
H006 2020-05-24 21:54:36



create table TEST(
ID number(5),
TIMEER DATE
);

Insert into TEST (ID,TIMEER) values ('H001','2020-05-24 02:39:30');
Insert into TEST (ID,TIMEER) values ('H001','2020-05-24 05:46:57');
Insert into TEST (ID,TIMEER) values ('H001','2020-05-24 20:12:40');
Insert into TEST (ID,TIMEER) values ('H002','2020-05-24 01:58:57');
Insert into TEST (ID,TIMEER) values ('H002','2020-05-24 04:54:06');
Insert into TEST (ID,TIMEER) values ('H002','2020-05-24 06:22:33');
Insert into TEST (ID,TIMEER) values ('H002','2020-05-24 20:45:15');
Insert into TEST (ID,TIMEER) values ('H003','2020-05-24 00:52:47');
Insert into TEST (ID,TIMEER) values ('H003','2020-05-24 13:31:59');
Insert into TEST (ID,TIMEER) values ('H005','2020-05-24 06:10:15');
Insert into TEST (ID,TIMEER) values ('H006','2020-05-24 02:11:12');
Insert into TEST (ID,TIMEER) values ('H006','2020-05-24 07:11:16');
Insert into TEST (ID,TIMEER) values ('H006','2020-05-24 10:26:08');
Insert into TEST (ID,TIMEER) values ('H006','2020-05-24 21:54:36');
Insert into TEST (ID,TIMEER) values ('H006','2020-05-24 22:26:29');
...全文
247 2 打赏 收藏 举报
写回复
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
sallkey 2020-05-25
drop table TEST purge ; create table TEST( ID varchar2(15), TIMEER DATE ); Insert into TEST (ID,TIMEER) values ('H001',to_date('2020-05-24 02:39:30','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H001',to_date('2020-05-24 05:46:57','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H001',to_date('2020-05-24 20:12:40','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H002',to_date('2020-05-24 01:58:57','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H002',to_date('2020-05-24 04:54:06','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H002',to_date('2020-05-24 06:22:33','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H002',to_date('2020-05-24 20:45:15','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H003',to_date('2020-05-24 00:52:47','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H003',to_date('2020-05-24 13:31:59','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H005',to_date('2020-05-24 06:10:15','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H006',to_date('2020-05-24 02:11:12','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H006',to_date('2020-05-24 07:11:16','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H006',to_date('2020-05-24 10:26:08','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H006',to_date('2020-05-24 21:54:36','yyyy-mm-dd hh24:mi:ss')); Insert into TEST (ID,TIMEER) values ('H006',to_date('2020-05-24 22:26:29','yyyy-mm-dd hh24:mi:ss')); with tmp as (select 'H001' id, to_date('2020-05-24 05:00:00','yyyy-mm-dd hh24:mi:ss') timeer from dual union all select 'H002' , to_date('2020-05-24 21:00:00','yyyy-mm-dd hh24:mi:ss') from dual union all select 'H006' , to_date('2020-05-24 22:00:00','yyyy-mm-dd hh24:mi:ss') from dual ) SELECT /*+parallel(t,4)*/ t.id,t.timeer from ( SELECT /*+parallel(t,4)*/ t.* , abs (tc.timeer - t.timeer) ,row_number() over (partition by t.id order by abs (tc.timeer - t.timeer) ) rn from TEST t inner join tmp tc on t.id =tc.id )t where t.rn =1;
  • 打赏
  • 举报
回复
js14982 2020-05-25

create table TEST(
       ID varchar2(5),
       TIMEER DATE    
       );

Insert into TEST (ID,TIMEER) values ('H001',to_date('2020-05-24 02:39:30','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H001',to_date('2020-05-24 05:46:57','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H001',to_date('2020-05-24 20:12:40','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H002',to_date('2020-05-24 01:58:57','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H002',to_date('2020-05-24 04:54:06','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H002',to_date('2020-05-24 06:22:33','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H002',to_date('2020-05-24 20:45:15','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H003',to_date('2020-05-24 00:52:47','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H003',to_date('2020-05-24 13:31:59','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H005',to_date('2020-05-24 06:10:15','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H006',to_date('2020-05-24 02:11:12','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H006',to_date('2020-05-24 07:11:16','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H006',to_date('2020-05-24 10:26:08','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H006',to_date('2020-05-24 21:54:36','yyyy-mm-dd hh24:mi:ss'));
Insert into TEST (ID,TIMEER) values ('H006',to_date('2020-05-24 22:26:29','yyyy-mm-dd hh24:mi:ss'));


with tt as
(select 'H001' id,   to_date('2020-05-24 05:00:00','yyyy-mm-dd hh24:mi:ss') timeer from dual union all
select 'H002'  ,  to_date('2020-05-24 21:00:00','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'H006'  ,  to_date('2020-05-24 22:00:00','yyyy-mm-dd hh24:mi:ss')from dual )
select id,timeer from
(select a.id,a.timeer,abs(a.timeer-b.timeer)timm1,min(abs(a.timeer-b.timeer))over(partition by a.id) timm2
from test a,tt b
where a.id = b.id 
) where timm1 = timm2;

  • 打赏
  • 举报
回复
相关推荐
发帖
Oracle

1.6w+

社区成员

Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2020-05-25 11:15
社区公告
暂无公告