Oracle update_date等于或小于给定日期的所有记录的最大update_date

ASKTOM 2019-09-26

261

问题描述

我想找到所有记录的最大update_date等于或小于给定日期的记录 (格式dd.mm.yyyy) (update_date <= :a_given_date)。

create table client (client_id number, tenant varchar2(2), client_name varchar2(100), update_date timestamp);

insert into client values(1000, 'FR', 'Michelin', systimestamp-4);
insert into client values(1000, 'FR', 'Michelin', systimestamp-3); 
insert into client values(1000, 'FR', 'Michelin', systimestamp-3);
insert into client values(1000, 'FR', 'Michelin', systimestamp-2/24); 
insert into client values(1000, 'FR', 'Michelin', systimestamp-1/24); 
insert into client values(1000, 'FR', 'Michelin', systimestamp);

commit;
复制

专家解答

SQL> create table client (client_id number, tenant varchar2(2), client_name varchar2(100), update_date timestamp);

Table created.

SQL>
SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp-4);

1 row created.

SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp-3);

1 row created.

SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp-3);

1 row created.

SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp-2/24);

1 row created.

SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp-1/24);

1 row created.

SQL> insert into client values(1000, 'FR', 'Michelin', systimestamp);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select *
  2  from   client
  3  where  update_date < date '2019-09-26'
  4  order by update_date desc
  5  fetch first 1 rows only;

 CLIENT_ID TE CLIENT_NAME          UPDATE_DATE
---------- -- -------------------- ---------------------------------------------------------------------------
      1000 FR Michelin             24-SEP-19 12.26.12.000000 PM
复制