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

5-13 ソートして、最初のレコードと外部結合

SQLパズル

会員登録テーブル
会員CD  氏名
------  ----
000001  モナ
000002  ギコ
000003  ぞぬ

イメージ登録テーブル
会員CD  ファイル名       写真取込日  写真貼付日
------  ------------   ----------  ----------
000001  monakao.jpg    2003/03/01  2003/03/01
000001  monakao2.jpg   2003/03/04  2003/03/04
000002  gikokao.jpg    2003/03/04  2003/03/04
000002  gikokao2.jpg   2003/03/04  2003/03/10
000003  zonukao.jpg    2003/03/04  2003/03/04

同一の会員コードのレコードがイメージ登録テーブルに存在する場合（同じ人で複数の写真が登録してある場合）
写真取込日が最大のレコード
ただし、写真取込日が最大のレコードが複数存在したら、写真貼付日が最大のレコード
ただし、写真貼付日も最大のレコードが複数存在したら、ファイル名 が最大のレコード
を取得する。

この二つをJoinさせるときにモナーでmonakao2.jpgを選択して結果として下記を出力する。

出力結果
会員CD  氏名     ファイル名    写真取込日   写真貼付日
------  -----  ------------  ----------   ----------
000001  モナ   monakao2.jpg  2003/03/04   2003/03/04
000002  ギコ    gikokao.jpg  2003/03/04   2003/03/10
000003  ぞぬ    zonukao.jpg  2003/03/04   2003/03/04

データ作成スクリプト

create table 会員登録(
会員CD char(6),
氏名 char(4));

create table イメージ登録(
会員CD char(6),
ファイル名 varchar2(12),
写真取込日 date,
写真貼付日 date);

insert into 会員登録(会員CD,氏名) values('000001','モナ');
insert into 会員登録(会員CD,氏名) values('000002','ギコ');
insert into 会員登録(会員CD,氏名) values('000003','ぞぬ');

insert into イメージ登録(会員CD,ファイル名,写真取込日,写真貼付日)
      select '000001','monakao.jpg' ,to_date('20030301','yyyymmdd'),to_date('20030301','yyyymmdd') from dual
union select '000001','monakao2.jpg',to_date('20030304','yyyymmdd'),to_date('20030304','yyyymmdd') from dual
union select '000002','gikokao.jpg' ,to_date('20030304','yyyymmdd'),to_date('20030304','yyyymmdd') from dual
union select '000002','gikokao2.jpg',to_date('20030304','yyyymmdd'),to_date('20030310','yyyymmdd') from dual
union select '000003','zonukao.jpg' ,to_date('20030304','yyyymmdd'),to_date('20030304','yyyymmdd') from dual;
commit;

SQL

--■■■Row_Number関数を使う方法■■■
select a.会員CD,a.氏名,b.ファイル名,b.写真取込日,b.写真貼付日
from 会員登録 a,
(select 会員CD,ファイル名,写真取込日,写真貼付日,
Row_Number() over (partition by 会員CD order by 写真取込日 desc,写真貼付日 desc,ファイル名 desc) as Rank
from イメージ登録) b
where a.会員CD = b.会員CD(+)
  and b.Rank(+) = 1
order by a.会員CD;

--■■■First_Value関数を使う方法■■■
select a.会員CD,a.氏名,b.ファイル名,b.写真取込日,b.写真貼付日
from 会員登録 a,
(select distinct 会員CD,
First_Value(ファイル名) over(partition by 会員CD order by 写真取込日 desc,写真貼付日 desc,ファイル名 desc)
as ファイル名,
First_Value(写真取込日) over(partition by 会員CD order by 写真取込日 desc,写真貼付日 desc,ファイル名 desc)
as 写真取込日,
First_Value(写真貼付日) over(partition by 会員CD order by 写真取込日 desc,写真貼付日 desc,ファイル名 desc)
as 写真貼付日
from イメージ登録) b
where a.会員CD = b.会員CD(+)
order by a.会員CD;

--■■■Last_Value関数を使う方法■■■
select a.会員CD,a.氏名,b.ファイル名,b.写真取込日,b.写真貼付日
from 会員登録 a,
(select distinct 会員CD,
Last_Value(ファイル名) over(partition by 会員CD order by 写真取込日,写真貼付日,ファイル名
Rows between Unbounded Preceding and Unbounded Following) as ファイル名,
Last_Value(写真取込日) over(partition by 会員CD order by 写真取込日,写真貼付日,ファイル名
Rows between Unbounded Preceding and Unbounded Following) as 写真取込日,
Last_Value(写真貼付日) over(partition by 会員CD order by 写真取込日,写真貼付日,ファイル名
Rows between Unbounded Preceding and Unbounded Following) as 写真貼付日
from イメージ登録) b
where a.会員CD = b.会員CD(+)
order by a.会員CD;

--■■■first関数を使う方法■■■
select a.会員CD,a.氏名,b.ファイル名,b.写真取込日,b.写真貼付日
from 会員登録 a,
(select 会員CD,
max(写真取込日) as 写真取込日,
max(写真貼付日) keep (dense_rank first order by 写真取込日 desc) as 写真貼付日,
max(ファイル名) keep (dense_rank first order by 写真取込日 desc,写真貼付日 desc) as ファイル名
from イメージ登録
group by 会員CD) b
where a.会員CD = b.会員CD(+)
order by a.会員CD;

--■■■Last関数を使う方法■■■
select a.会員CD,a.氏名,b.ファイル名,b.写真取込日,b.写真貼付日
from 会員登録 a,
(select 会員CD,
max(写真取込日) as 写真取込日,
max(写真貼付日) keep (dense_rank Last order by 写真取込日) as 写真貼付日,
max(ファイル名) keep (dense_rank Last order by 写真取込日,写真貼付日) as ファイル名
from イメージ登録
group by 会員CD) b
where a.会員CD = b.会員CD(+)
order by a.会員CD;

--■■■相関サブクエリとインラインビューを使う方法1■■■
select a.会員CD,a.氏名,b.ファイル名,b.写真取込日,b.写真貼付日
from 会員登録 a,
(select c.会員CD,c.ファイル名,c.写真取込日,c.写真貼付日 from イメージ登録 c
 where (select count(d.会員CD) from イメージ登録 d
         where d.会員CD=c.会員CD
           and d.RowID!=c.RowID
           and d.写真取込日 >= c.写真取込日) = 0
    or (select count(d.会員CD) from イメージ登録 d
         where d.会員CD=c.会員CD
           and d.RowID!=c.RowID
           and d.写真取込日 = (select max(e.写真取込日) from イメージ登録 e
                                where e.会員CD=c.会員CD)
           and d.写真貼付日 >= c.写真貼付日) = 0
    or (select count(d.会員CD) from イメージ登録 d
         where d.会員CD=c.会員CD
           and d.RowID!=c.RowID
           and d.写真取込日 = (select max(e.写真取込日) from イメージ登録 e
                                where e.会員CD=c.会員CD)
           and d.写真貼付日 = (select max(e.写真貼付日) from イメージ登録 e
                                where e.会員CD=c.会員CD)
           and d.ファイル名 >= c.ファイル名) = 0) b
where a.会員CD = b.会員CD(+)
order by a.会員CD;

--■■■相関サブクエリとインラインビューを使う方法2■■■
select a.会員CD,a.氏名,b.ファイル名,b.写真取込日,b.写真貼付日
from 会員登録 a,
(select c.会員CD,c.ファイル名,c.写真取込日,c.写真貼付日 from イメージ登録 c
  where not exists(select 1 from イメージ登録 d
                    where d.会員CD=c.会員CD
                      and d.RowID!=c.RowID
                      and d.写真取込日 >= c.写真取込日)
     or not exists(select 1 from イメージ登録 d
                    where d.会員CD=c.会員CD
                      and d.RowID!=c.RowID
                      and d.写真取込日=(select max(e.写真取込日) from イメージ登録 e
                                         where e.会員CD=c.会員CD)
                      and d.写真貼付日 >= c.写真貼付日)
     or not exists(select 1 from イメージ登録 d
                    where d.会員CD=c.会員CD
                      and d.RowID!=c.RowID
                      and d.写真取込日=(select max(e.写真取込日) from イメージ登録 e
                                         where e.会員CD=c.会員CD)
                      and d.写真貼付日=(select max(e.写真貼付日) from イメージ登録 e
                                         where e.会員CD=c.会員CD)
                      and d.ファイル名 >= c.ファイル名)) b
where a.会員CD = b.会員CD(+)
order by a.会員CD;

解説

Row_Number関数を使えば、パーティションを切った中でソートして、順位を取得できます。