10-4 最新の更新日を取得

SQLパズル

社員テーブル
社員コード   担当職務コード     勤務開始日
----------   ------------     ----------
        1               A     2005/04/01
        1               B     2005/05/01
        1               A     2005/06/01
        1               A     2005/07/01
        1               A     2005/08/01
        2               B     2005/07/01
        2               A     2005/08/01
        2               B     2005/09/01
        3               B     2005/10/01
        4               A     2005/11/01

社員テーブルから、社員ごとの最新の担当職務コード更新日を取得する。

出力結果
社員コード   担当職務コード     勤務開始日
----------   ------------     ----------
        1               A     2005/06/01
        2               B     2005/09/01
        3               B     2005/10/01
        4               A     2005/11/01

データ作成スクリプト

create table 社員テーブル(
社員コード     number(1),
担当職務コード char(1),
勤務開始日     date,
primary key(社員コード,勤務開始日));

insert into 社員テーブル values(1,'A',to_date(20050401,'yyyymmdd'));
insert into 社員テーブル values(1,'B',to_date(20050501,'yyyymmdd'));
insert into 社員テーブル values(1,'A',to_date(20050601,'yyyymmdd'));
insert into 社員テーブル values(1,'A',to_date(20050701,'yyyymmdd'));
insert into 社員テーブル values(1,'A',to_date(20050801,'yyyymmdd'));
insert into 社員テーブル values(2,'B',to_date(20050701,'yyyymmdd'));
insert into 社員テーブル values(2,'A',to_date(20050801,'yyyymmdd'));
insert into 社員テーブル values(2,'B',to_date(20050901,'yyyymmdd'));
insert into 社員テーブル values(3,'B',to_date(20051001,'yyyymmdd'));
insert into 社員テーブル values(4,'A',to_date(20051101,'yyyymmdd'));
commit;

SQL

col 勤務開始日 for a20

--■■■Keepを使う方法■■■
select 社員コード,
max(担当職務コード) Keep(Dense_Rank First order by 勤務開始日) as 担当職務コード,
to_char(max(勤務開始日),'YYYY/MM/DD') as 勤務開始日
from (select 社員コード,担当職務コード,勤務開始日,
      Lag(担当職務コード) over(partition by 社員コード order by 勤務開始日) as LagVal
        from 社員テーブル)
where LagVal is null
   or 担当職務コード != LagVal
group by 社員コード
order by 社員コード;

--■■■分析関数を使う方法1■■■
select distinct 社員コード,
Last_Value(担当職務コード) over(partition by 社員コード order by 勤務開始日
Rows between Unbounded Preceding and Unbounded Following) as 担当職務コード,
to_char(max(勤務開始日) over(partition by 社員コード),'YYYY/MM/DD') as 勤務開始日
from (select 社員コード,担当職務コード,
      Lag(担当職務コード) over(partition by 社員コード order by 勤務開始日) as Lag担当職務コード,
      勤務開始日
      from 社員テーブル)
where Lag担当職務コード is null
   or Lag担当職務コード != 担当職務コード
order by 社員コード;

--■■■分析関数を使う方法2■■■
select distinct 社員コード,
担当職務コード,
to_char(min(勤務開始日) over(partition by 社員コード),'YYYY/MM/DD') as 勤務開始日
from 社員テーブル a
where not exists(select 1 from 社員テーブル b
                  where b.社員コード=a.社員コード
                    and b.担当職務コード!=a.担当職務コード
                    and b.勤務開始日 > a.勤務開始日)
order by 社員コード;

--■■■相関サブクエリを使う方法■■■
select 社員コード,担当職務コード,to_char(勤務開始日,'YYYY/MM/DD') as 勤務開始日
  from 社員テーブル a
 where 勤務開始日 =
(select min(b.勤務開始日) from 社員テーブル b
  where b.社員コード=a.社員コード
    and b.担当職務コード=(select c.担当職務コード from 社員テーブル c
                           where c.社員コード=b.社員コード
                             and c.勤務開始日=(select max(d.勤務開始日) from 社員テーブル d
                                                where d.社員コード=c.社員コード))
    and not exists(select 1 from 社員テーブル e
                    where e.社員コード = b.社員コード
                      and e.担当職務コード != b.担当職務コード
                      and e.勤務開始日 > b.勤務開始日))
order by 社員コード;

--■■■相関サブクエリを使う方法(上のクエリを簡略化)■■■
select 社員コード,担当職務コード,to_char(勤務開始日,'YYYY/MM/DD') as 勤務開始日
  from 社員テーブル a
 where 勤務開始日 =
(select min(b.勤務開始日) from 社員テーブル b
 where b.社員コード=a.社員コード
   and not exists(select 1 from 社員テーブル c
                   where c.社員コード = b.社員コード
                     and c.担当職務コード != b.担当職務コード
                     and c.勤務開始日 > b.勤務開始日))
order by 社員コード;

解説

相関サブクエリを使う方法での自己結合の結合条件では、
a.社員コード=b.社員コードかつ
b.社員コード=c.社員コードかつ
c.社員コード=d.社員コードかつ
b.社員コード=e.社員コード
ゆえにa.社員コード=b.社員コード=c.社員コード=d.社員コード=e.社員コード
といった三段論法を使ってます。
三段論法の知識があると、結合の多いSQLを書きやすくなります。