表の取り出し方で困ってます

質問

以下のような2つのテーブルがあるとして、

[表A：学生マスター]
学生ID　氏名
001　　　佐藤
002　　　斉藤
003　　　田中

[表B：試験結果]
学生ID　試験ID　得点
001　　　001　　　80
002　　　001　　　70
003　　　001　　　60
001　　　002　　　75
003　　　002　　　85
002　　　003　　　90
003　　　003　　　85

以下のような集計表を得たいのですが、一発で作成できる（しかも高速な）SQLが思いつきません。
試験結果テーブルに順位が入っていれば簡単なのですが。
どなたかご教示ください。
[集計表]
学生ID　試験1　順位　試験2　順位　試験3　順位　平均得点
001　　　　　80　　　1　　　75　　　2　　　欠席　　　　　 77.5
002　　　　　70　　　2　　　欠席　　　　　 90　　　1　　　80.0
003　　　　　60　　　3　　　85　　　1　　　85　　　2　　　76.7

回答No.2

chukenkenkou

>まず学生テーブルをスキャンし、その各行に対して試験ごとの結果を得る、
>という方法でやってみたのですが遅いので。

どんなSQLか、具体的に提示できませんか？
「遅い」と言われても、クエリの記述レベルでどうなるものか、母体データ件数などを含めて示してもらわないと判断できません。

>DBへの書き込み権限がないのと、

性能を出すため、インデクスの定義を見直すことはできるのですよね？
そうでないなら、諦めてください。

>同じ試験問題を後から受ける学生がいるため、順位をあらかじめ入れて
>おくことができません。

成績表を作成する直前に、順位を入れればいいのでは？

>> 順位を求めるクエリは、自己結合すればできますよね？
> 上記のような方法でやってみました。

具体的にどんなSQLなのか、提示できませんか？

「遅い」、「そういう方法でやっている」と日本語だけで示されても、そのクエリが適切なものかどうか判断できず、具体的なアドバイスができません。

> それと、RDBMSは、PostgreSQLなのですよね？バージョンは？
> 7.3.18です。

このサイトでは、丸投げは禁止事項ですよ？
自分で考えた内容を示し、具体的に分からない部分を質問することが規約になっています。仕様を提示して、「SQLを作って」といったものは、「質問でなく作業依頼」などとして、せっかく回答しても削除される可能性があります。

PostgreSQL 7.3.18で動くかどうか分かりませんが、8.0では動くSQL例を示します。
1行内で同じ列名でタイトルを出す、同じ列の値として数値と文字を混在して出したりは、すべて文字にキャストしたりしないと実現できませんから念のため。

-- 成績表
select
g.gid,
max(case sid when 1 then tokuten else null end) as 試験1,
max(case sid when 1 then rk else null end) as 順位1,
max(case sid when 2 then tokuten else null end) as 試験2,
max(case sid when 2 then rk else null end) as 順位2,
max(case sid when 3 then tokuten else null end) as 試験3,
max(case sid when 3 then rk else null end) as 順位3,
cast(avg(tokuten) as dec(4,1)) as 平均得点
from gakusei as g
left join
(select
gid,
sid,
tokuten,
(select count(*)+1
from shiken
where sid=x.sid
and tokuten>x.tokuten) as rk
from shiken as x) as s
on g.gid=s.gid
group by g.gid
order by g.gid

お礼

gooから投稿があったことのお知らせが今日届いたので、お礼が遅くなりました。

ご指摘の点は理解できますが、何しろこのDBには書き込み権限がないため、順位を入れておいて集計する、という方法は使えません。
インデックスの問題にしても、担当者にその気がない（そもそも順位を集計する目的に使っていない）みたいで、実現は難しいです。

結局のところ、行と列を入れ替えて、行を試験単位にし、列に学生を並べることでcsv形式に出力し、Excelで行と列を入れ替えて配布することになりました。

貴重なご意見ありがとうございました。

回答No.1

chukenkenkou

>一発で作成できる（しかも高速な）SQLが思いつきません。

複数のSQLか、低速なSQLなら、自分で思いつくということですか？

>試験結果テーブルに順位が入っていれば簡単なのですが。

そう思うなら、順位を予め入れておけばいいのでは？
順位を求めるクエリは、自己結合すればできますよね？

それと、RDBMSは、PostgreSQLなのですよね？バージョンは？

補足

> 複数のSQLか、低速なSQLなら、自分で思いつくということですか？

まず学生テーブルをスキャンし、その各行に対して試験ごとの結果を得る、という方法でやってみたのですが遅いので。

> そう思うなら、順位を予め入れておけばいいのでは？

DBへの書き込み権限がないのと、同じ試験問題を後から受ける学生がいるため、順位をあらかじめ入れておくことができません。

> 順位を求めるクエリは、自己結合すればできますよね？

上記のような方法でやってみました。

> それと、RDBMSは、PostgreSQLなのですよね？バージョンは？

7.3.18です。
サーバースペックについてはわかりません。