サブクエリを使用せずにLEFT JOIN + GROUP BY + ORDER BYでデータの取得

課題テーブル：

1　課題１　Aさん
2　課題１　Bさん
3　課題１　Cさん
4　課題１　Dさん
5　課題１　Eさん
6　課題２　Bさん
7　課題２　Cさん

成績テーブル：

Aさん　算数　1
Aさん　国語　2
Bさん　算数　3
Cさん　算数　2
Dさん　国語　1

目的：
課題１を算数の成績順に取得したい。


普通にLEFT JOINすると以下のようになる。

1　課題１　Aさん　算数　1
1　課題１　Aさん　国語　2
2　課題１　Bさん　算数　3
3　課題１　Cさん　算数　2
4　課題１　Dさん　国語　1
5　課題１　Eさん　NULL　NULL

ここから名前を１つにまとめて、成績順に。

1　課題１　Aさん　算数　1
3　課題１　Cさん　算数　2
2　課題１　Bさん　算数　3
4　課題１　Dさん　NULL　NULL
5　課題１　Eさん　NULL　NULL

このようにしたいのですが、名前でGROUP BYすると科目と成績の整合性がとれなくなってしまい、正しく順位づけできません。

ORDER BY CASE
　WHEN 成績.科目 = '算数' THEN '0'
　WHEN 成績.科目 IS NULL THEN '2'
　ELSE '1'
END, 成績.科目, 成績.成績


サブクエリを使わずに実行する方法は、何かないものでしょうか。

No.1 ベストアンサー 回答者： yambejp 回答日時： 2008/07/03 14:53

LEFT JOINするときに条件で科目=算数をいれておいて、

WHEREで課題１をチョイスすればGROUP BY するまでもなく人は
ユニークになると思いますが・・・

こんなかんじ

SELECT *
FROM 課題
LEFT JOIN 成績 ON 課題.人=成績.人
AND 科目='算数'
WHERE 課題='課題１'
ORDER BY COALESCE(順位,999)

この回答へのお礼

なるほど！！
COALESCEを使えばうまく動きますね！

どうもありがとうございました。
本当に助かりました！

お礼日時：2008/07/03 16:29