トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

10-42 過去で最大のデータと結合

SQLパズル

レンタルテーブル
号車  利用開始日   利用者名
----  ----------  --------
 105  1998/7/24   AAA
 105  1998/11/17  BBB
 105  2001/4/1    CCC
 110  2005/1/1    DDD
 110  2005/11/11  EEE
 112  2005/12/20  FFF

走行テーブル
号車  走行日       走行距離
----  ----------  --------
 105  1998/8/1          10
 105  1998/10/15        20
 105  2001/6/22         15
 110  2005/9/30         30
 110  2005/11/3         35
 112  2005/12/22        40

レンタルテーブルと走行テーブルから
利用履歴を出力する。

出力結果
号車  利用者名   走行日      走行距離
----  --------  ----------  --------
 105  AAA       1998/8/1          10
 105  AAA       1998/10/15        20
 105  CCC       2001/6/22         15
 110  DDD       2005/9/30         30
 110  DDD       2005/11/3         35
 112  FFF       2005/12/22        40


データ作成スクリプト

create table レンタルテーブル(
号車       number(3),
利用開始日 varchar2(10),
利用者名   char(3));

insert into レンタルテーブル values(105,'1998/7/24' ,'AAA');
insert into レンタルテーブル values(105,'1998/11/17','BBB');
insert into レンタルテーブル values(105,'2001/4/1'  ,'CCC');
insert into レンタルテーブル values(110,'2005/1/1'  ,'DDD');
insert into レンタルテーブル values(110,'2005/11/11','EEE');
insert into レンタルテーブル values(112,'2005/12/20','FFF');

create table 走行テーブル(
号車     number(3),
走行日   varchar2(10),
走行距離 number(3));

insert into 走行テーブル values(105,'1998/8/1'  ,10);
insert into 走行テーブル values(105,'1998/10/15',20);
insert into 走行テーブル values(105,'2001/6/22' ,15);
insert into 走行テーブル values(110,'2005/9/30' ,30);
insert into 走行テーブル values(110,'2005/11/3' ,35);
insert into 走行テーブル values(112,'2005/12/22',40);
commit;


SQL

--■■■分析関数とignore nullsを使う方法(10g以降)■■■
select distinct b.号車,
Last_Value(case when to_date(b.走行日,'FMYYYY/MM/DD')
                  >= to_date(a.利用開始日,'FMYYYY/MM/DD')
                then a.利用者名 end ignore nulls)
over(partition by b.号車,b.走行日
order by to_date(a.利用開始日,'FMYYYY/MM/DD')
Rows between Unbounded Preceding and Unbounded Following) as 利用者名,
b.走行日,b.走行距離
 from レンタルテーブル a,走行テーブル b
where a.号車 = b.号車
order by b.号車,to_date(b.走行日,'FMYYYY/MM/DD');

--■■■相関サブクエリを使う方法■■■
select b.号車,a.利用者名,b.走行日,b.走行距離
  from レンタルテーブル a,走行テーブル b
 where a.号車 = b.号車
   and to_date(a.利用開始日,'FMYYYY/MM/DD') =
(select max(to_date(c.利用開始日,'FMYYYY/MM/DD')) from レンタルテーブル c
  where c.号車=b.号車
    and to_date(c.利用開始日,'FMYYYY/MM/DD') <= to_date(b.走行日,'FMYYYY/MM/DD'))
order by b.号車,to_date(b.走行日,'FMYYYY/MM/DD');


解説

to_date関数の書式指定でフルモードをオフにして、date型にキャストしてます。