5-47 他行を優先する出力その1

SQLパズル

試験テーブル
         試験名    名前        科目   点数
---------------  ------  ----------  ----
2005年1学期中間   ジョン  英語          98
2005年1学期中間   ジョン  数学          32
2005年1学期中間   ジョン  数学(追試)    32
2005年1学期期末   ジョン  英語          99
2005年1学期期末   ジョン  数学          96
2005年1学期中間   権兵衛  英語          50
2005年1学期中間   権兵衛  数学          32
2005年1学期中間   権兵衛  数学(追試)    58
2005年1学期期末   権兵衛  英語          30
2005年1学期期末   権兵衛  英語(追試)    10
2005年1学期期末   権兵衛  数学          67

試験テーブルのレコードを出力する
ただし、追試を受けた場合は、追試のレコードのみを出力する。

出力結果
         試験名    名前        科目   点数
---------------  ------  ----------  ----
2005年1学期中間   ジョン  英語          98
2005年1学期中間   ジョン  数学(追試)    32
2005年1学期期末   ジョン  英語          99
2005年1学期期末   ジョン  数学          96
2005年1学期中間   権兵衛  英語          50
2005年1学期中間   権兵衛  数学(追試)    58
2005年1学期期末   権兵衛  英語(追試)    10
2005年1学期期末   権兵衛  数学          67

データ作成スクリプト

create table 試験(
試験名 varchar2(15),
名前   varchar2(6),
科目   varchar2(10),
点数   number(3));

insert into 試験 values('2005年1学期中間','ジョン','英語'      ,98);
insert into 試験 values('2005年1学期中間','ジョン','数学'      ,32);
insert into 試験 values('2005年1学期中間','ジョン','数学(追試)',32);
insert into 試験 values('2005年1学期期末','ジョン','英語'      ,99);
insert into 試験 values('2005年1学期期末','ジョン','数学'      ,96);
insert into 試験 values('2005年1学期中間','権兵衛','英語'      ,50);
insert into 試験 values('2005年1学期中間','権兵衛','数学'      ,32);
insert into 試験 values('2005年1学期中間','権兵衛','数学(追試)',58);
insert into 試験 values('2005年1学期期末','権兵衛','英語'      ,30);
insert into 試験 values('2005年1学期期末','権兵衛','英語(追試)',10);
insert into 試験 values('2005年1学期期末','権兵衛','数学'      ,67);
commit;

SQL

--■■■分析関数を使う方法■■■
select distinct 試験名,名前,
Last_Value(科目) over(partition by 試験名,名前,Replace(科目,'(追試)')
order by instr(科目,'(追試)') Rows between Unbounded Preceding and Unbounded Following) as 科目,
Last_Value(点数) over(partition by 試験名,名前,Replace(科目,'(追試)')
order by instr(科目,'(追試)') Rows between Unbounded Preceding and Unbounded Following) as 点数
from 試験
order by 名前,試験名 desc,科目;

--■■■case式を使う方法■■■
select 試験名,名前,
case when exists(select 1 from 試験 b
                  where b.試験名 = a.試験名
                    and b.名前   = a.名前
                    and instr(b.科目,'(追試)') > 0
                    and replace(b.科目,'(追試)') = a.科目)
     then (select b.科目 from 試験 b
                  where b.試験名 = a.試験名
                    and b.名前   = a.名前
                    and instr(b.科目,'(追試)') > 0
                    and replace(b.科目,'(追試)') = a.科目) else 科目 end as 科目,
case when exists(select 1 from 試験 b
                  where b.試験名 = a.試験名
                    and b.名前   = a.名前
                    and instr(b.科目,'(追試)') > 0
                    and replace(b.科目,'(追試)') = a.科目)
     then (select b.点数 from 試験 b
                  where b.試験名 = a.試験名
                    and b.名前   = a.名前
                    and instr(b.科目,'(追試)') > 0
                    and replace(b.科目,'(追試)') = a.科目) else 点数 end as 点数
from 試験 a
where instr(科目,'(追試)') = 0
order by 名前,試験名 desc,科目;

--■■■外部結合を使う方法■■■
select a.試験名,a.名前,
nvl(b.科目,a.科目) as 科目,
nvl(b.点数,a.点数) as 点数
from 試験 a Left Join 試験 b
on (a.試験名 = b.試験名
and a.名前   = b.名前
and a.科目   = replace(b.科目,'(追試)')
and instr(b.科目,'(追試)') > 0)
where instr(a.科目,'(追試)') = 0
order by 名前,試験名 desc,科目;

解説

instr関数で追試かチェックしてます。