条件ごとに最大値を持つデータの他のカラムを取得

質問

日付、天気、気温が並んでいるテーブルがあるとします。
そこからデータを抽出して天気ごとにグループ化し、
「天気」「最高気温」「最高気温が出た日付(複数あれば新しいほう)」「20℃以上になった最新の日付」
というビューを作りたいのですが、書き方がわかりません。
単純にグループ化すると最高気温はmaxで出せますが、日付もmaxにすると気温と関係のない最新の日付になってしまいます。
条件をつけたい場合どのようにすればいいか教えてください。

回答No.1

yambejp

細かくチェックしてないけどこんな感じでどうでしょう？

create table tenki_log(日付 date,天気 varchar(10),気温 double);
insert into tenki_log values(
'2012-10-01','晴',19.5),(
'2012-10-02','晴',19.0),(
'2012-10-03','晴',20.0),(
'2012-10-04','晴',21.0),(
'2012-10-05','晴',21.0),(
'2012-10-06','晴',19.5),(
'2012-10-07','晴',20.5),(
'2012-10-08','曇',18.0),(
'2012-10-09','曇',21.0),(
'2012-10-10','曇',20.0),(
'2012-10-11','雨',18.0),(
'2012-10-12','雨',18.0),(
'2012-10-13','雨',18.0),(
'2012-10-14','雨',17.0),(
'2012-10-15','曇',18.0),(
'2012-10-16','晴',18.0);

//表示

select `天気`
,`最高気温`
,`最高気温が出た日付`
,(select max(`日付`)
from tenki_log
where `気温`>=20
and `天気`=sub.`天気`
) as `20度以上になった最新の日付`
from (
select `天気` ,`気温` as `最高気温`,max(`日付`) as `最高気温が出た日付`
from tenki_log
where (`天気`,`気温`) in (
select `天気`,max(`気温`)
from tenki_log
group by `天気`
)
group by `天気`
) as sub