トップページに戻る
次の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型にキャストしてます。