在最接近日期中查找相关表中的行

Question

我需要在bonus表中的每一行中找到service.service_id，service.name和service.date_begin，其中最接近service.date_begin和service.date_begin <= bonus.date_begin。如果此类date_begin有多个service，请返回任何service(例如，使用MAX service.service_id或service.rowid(。如果没有这样的service，请返回NULL。

示例

bonus表(bonus_id是PK(：

bonus_id  date_begin
--------------------
1         2010-04-12
2         2010-04-20

service表(service_id是PK(：

bonus_id  service_id  name  date_begin
--------------------------------------
1         1           'a'   2010-04-10
1         2           'b'   2010-04-11
1         3           'c'   2010-04-11
1         4           'd'   2010-04-15
2         5           'e'   2010-04-22

所需的输出：

bonus_id  bonus_date_begin  service_id  service_name  service_date_begin
------------------------------------------------------------------------
1         2010-04-12        3           'c'           2010-04-11
2         2010-04-20        NULL        NULL          NULL

数据库：Oracle 11.2

人口脚本：

create table bonus (
  bonus_id number primary key,
  date_begin date
);
create table service (
  bonus_id number references bonus(bonus_id),
  service_id number primary key,
  name varchar2(1),
  date_begin date
);
insert into bonus values (1, date '2010-04-12');
insert into bonus values (2, date '2010-04-20');
insert into service values (1, 1, 'a', date '2010-04-10');
insert into service values (1, 2, 'b', date '2010-04-11');
insert into service values (1, 3, 'c', date '2010-04-11');
insert into service values (1, 4, 'd', date '2010-04-15');
insert into service values (2, 5, 'e', date '2010-04-22');
commit;

Answer 1

SELECT b.bonus_id,
       MAX( b.date_begin ) AS bonus_date_begin,
       MAX( s.service_id ) KEEP ( DENSE_RANK LAST ORDER BY s.date_begin, s.service_id )
         AS service_id,
       MAX( s.name       ) KEEP ( DENSE_RANK LAST ORDER BY s.date_begin, s.service_id )
         AS service_name,
       MAX( s.date_begin ) KEEP ( DENSE_RANK LAST ORDER BY s.date_begin, s.service_id )
         AS service_date_begin
FROM   bonus b
       LEFT OUTER JOIN
       service s
       ON ( b.bonus_id = s.bonus_id AND s.date_begin < b.date_begin )
GROUP BY b.bonus_id;

输出：

BONUS_ID BONUS_DATE_BEGIN SERVICE_ID SERVICE_NAME SERVICE_DATE_BEGIN
-------- ---------------- ---------- ------------ ------------------
1        2010-04-12       3          c            2010-04-11
2        2010-04-20       NULL       NULL         NULL

Answer 2

尝试此代码，这将提供您所需的输出。

select bonus.bonus_id ,bonus.date_begin as bonus_date_begin,max(service.service_id) as service_id,
max(service.date_begin) as service_date_begin 
from bonus left join service
on bonus.bonus_id=service.bonus_id
and service.date_begin <= bonus.date_begin
group by bonus.bonus_id ,bonus.date_begin

Answer 3

select
  t.bonus_id,
  t.bonus_date_begin,
  t.service_id,
  t.service_name,
  t.service_date_begin
from (
  select
    b.bonus_id,
    b.date_begin as bonus_date_begin,
    s.service_id,
    s.name as service_name,
    s.date_begin as service_date_begin,
    row_number() over (
      partition by b.bonus_id
      order by s.date_begin desc, s.service_id desc
    ) as rn
  from avd_bonus b
  left join avd_service s on b.bonus_id = s.bonus_id and s.date_begin <= b.date_begin
) t
where t.rn = 1;