5-49 他行を優先する出力その3

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学期中間   ジョン  数学(追試)    32
2005年1学期期末   ジョン  英語          99
2005年1学期期末   ジョン  数学          96
2005年1学期中間   権兵衛  英語          50
2005年1学期中間   権兵衛  数学(追試)    58
2005年1学期期末   権兵衛  英語          30
2005年1学期期末   権兵衛  数学          67

データ作成スクリプト

前のSQLパズルと同じ

SQL

--■■■分析関数を使う方法■■■
select 試験名,名前,科目,点数
from (select 試験名,名前,科目,点数,
      max(点数) over(partition by 試験名,名前,Replace(科目,'(追試)')) as Max点数
      from 試験)
where 点数 = Max点数
order by 名前,試験名 desc,科目;

--■■■相関サブクエリを使う方法■■■
select 試験名,名前,科目,点数
  from 試験 a
 where 点数 = (select max(b.点数)
                 from 試験 b
                where b.試験名 = a.試験名
                  and b.名前   = a.名前
                  and replace(b.科目,'(追試)') = replace(a.科目,'(追試)'))
order by 名前,試験名 desc,科目;

解説

where句で最大の点数と等しいかチェックしてます。