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

9-21 最後の二回の給料

SQLパズル

Salariesテーブル
emp    sal_date    sal_amt
-----  ----------  -------
Dick   1996/06/20      500
Harry  1996/07/20      500
Harry  1996/09/20      700
Tom    1996/06/20      500
Tom    1996/08/20      700
Tom    1996/10/20      800
Tom    1996/12/20      900

empごとのsal_dateの昇順で
最後の二回の、sal_dateとsal_amtを以下の形式で出力する

出力結果
emp    sal_date1   sal_amt1  sal_date2   sal_amt2
-----  ----------  --------  ----------  --------
Dick   1996/06/20       500        null      null
Harry  1996/09/20       700  1996/07/20       500
Tom    1996/12/20       900  1996/10/20       800

SQLパズル(日本語版)のパズル14 [最後の2回の昇給] を参考にさせていただきました
SQLパズル 第2版のパズル15 [現在の給料と昇給前の給料] を参考にさせていただきました


データ作成スクリプト

create table Salaries(
emp      varchar2(6) not null,
sal_date date        not null,
sal_amt  number(3)   not null,
primary key(emp,sal_date));

insert into Salaries values('Dick' ,to_date('1996/06/20','YYYY/MM/DD'),500);
insert into Salaries values('Harry',to_date('1996/07/20','YYYY/MM/DD'),500);
insert into Salaries values('Harry',to_date('1996/09/20','YYYY/MM/DD'),700);
insert into Salaries values('Tom'  ,to_date('1996/06/20','YYYY/MM/DD'),500);
insert into Salaries values('Tom'  ,to_date('1996/08/20','YYYY/MM/DD'),700);
insert into Salaries values('Tom'  ,to_date('1996/10/20','YYYY/MM/DD'),800);
insert into Salaries values('Tom'  ,to_date('1996/12/20','YYYY/MM/DD'),900);
commit;


SQL

--■■■分析関数を使う方法■■■
select emp,
to_char(sal_date1,'YYYY/MM/DD') as sal_date1,sal_amt1,
to_char(sal_date2,'YYYY/MM/DD') as sal_date2,sal_amt2
from (select emp,
      max(sal_date) over(partition by emp) as maxsal_date,
      sal_date as sal_date1,
      sal_amt as sal_amt1,
      Lag(sal_date) over(partition by emp order by sal_date) as sal_date2,
      Lag(sal_amt)  over(partition by emp order by sal_date) as sal_amt2
         from Salaries)
where sal_date1 = maxsal_date
order by emp;

--■■■分析関数を使わない方法■■■
select emp,
to_char(max(decode(Rank,1,sal_date)),'YYYY/MM/DD') as sal_date1,
max(decode(Rank,1,sal_amt))  as sal_amt1,
to_char(max(decode(Rank,2,sal_date)),'YYYY/MM/DD') as sal_date2,
max(decode(Rank,2,sal_amt))  as sal_amt2
from (select emp,sal_date,sal_amt,
       (select count(*)+1
          from Salaries b
         where b.emp = a.emp
           and b.sal_date > a.sal_date) as Rank
        from Salaries a)
group by emp
order by emp;


解説

分析関数を使わない方法では、
順位を求めてグループ化してます