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');

...全文

375 2 打赏收藏转发到动态举报

写回复

用AI写文章

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;