サブクエリを使用せずに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 · ベストアンサー

LEFT JOINするときに条件で科目=算数をいれておいて、
WHEREで課題１をチョイスすればGROUP BY するまでもなく人は
ユニークになると思いますが・・・

こんなかんじ

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

お礼

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

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