トップページに戻る
次の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;
解説
分析関数を使わない方法では、
順位を求めてグループ化してます