5-48 他行を優先する出力その2

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学期期末   権兵衛  英語          30
2005年1学期期末   権兵衛  数学          67

データ作成スクリプト

前のSQLパズルと同じ

SQL

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

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

解説

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