年度ごとの最大値

日付と金額のテーブルから年度ごとの最大金額とその日付を抽出する方法が分かりません。どなたか教えてください。

日付 ,金額
2004/04/10,550000
2004/04/10,210000
2004/04/10,975000
2004/04/11,515000
2004/04/11,189000
2004/04/11,884500
2004/04/12,445500
2004/04/12,150000

よろしくお願いします。

No.9 ベストアンサー 回答者： ngsvx 回答日時： 2005/04/22 14:34

>これは、2004/1/1から2004/12/31までの最大金額を抽出する場合には、『2004』ということでしょうか。

そうです。

年なのか年度なのかわからなかったので、柔軟にするためにこうしました。

たとえば、2004/03/01というレコードのyearフィールドは、
年度であれば2003となり、年であれば2004が入るものとしています。

今回は「年」でいいということですので、フィールドを使う代わりに、関数で年だけを抜き出しても構いません。

ＳＱＬ解説

select t1.ymd, t1.price
from tbl AS t1
where t1.price=XXXX
order by t1.ymd;

だと、売上金額がXXXXであるレコードをすべて表示する。

このＳＱＬのXXXXを実行時に変化させることで、目的のレコードが拾える。


t1.price=(select max(t2.price) from tbl as t2 where t2.year=t1.year)

XXXXを実行時に変化させるためのサブクエリー。
「今対象としているレコードの年（year）と同じ年のレコードの内、売上金額が最大のもの」

という意味。


組合わせると、
「自分と同じ年を持つレコードの内の最大の売上金額を持つレコード」
が出力対象となります。

この回答への補足

非常に丁寧な解説ありがとうございました。
しかしこのクエリを実行すると、
『クエリ式 't2.2004=t1.2004' の'.'、'!'、または'()'の使い方が正しくありません。』
と言われてしまい実行できませんでした。

補足日時：2005/04/23 03:26

No.10 回答者： ngsvx 回答日時： 2005/04/25 09:06

yearは、あくまでもフィールド名です。

基本的には、#8のＳＱＬはそのまま使ってください。

No.8 回答者： ngsvx 回答日時： 2005/04/21 11:48

#6です。

まだ不明な点が２つあります。

１．結局、「年ごと」なのか「年度ごと」なのか
２．期間内にもっとも多い金額の日が複数あったらどうするか


補足要求ばかりでもなんですので、次の条件のＳＱＬです。

・年ごと
・最大金額が複数あるときは、すべて出力
・テーブル名はtblとします。
・フィールドは、
　year　期間を表す年
　ymd　　売上発生の年月日
　price　売上金額
　とします。


select t1.ymd, t1.price
from tbl AS t1
where t1.price=(select max(t2.price) from tbl as t2 where t2.year=t1.year)
order by t1.ymd;

この回答への補足

回答ありがとうございます。
不明な点にお答えします。

１．結局、「年ごと」なのか「年度ごと」なのか
→『年ごと』です。例えば、2004/1/1から2004/12/31までの中での最大金額です。

２．期間内にもっとも多い金額の日が複数あったらどうするか
→期間内にもっとも多い金額の日が複数日あった場合は、すべてが必要です。

上記のＳＱＬについて質問です。

　year　期間を表す年

これは、2004/1/1から2004/12/31までの最大金額を抽出する場合には、『2004』ということでしょうか。

よろしくお願いします。

補足日時：2005/04/22 14:01

No.7 回答者： pierrot666 回答日時： 2005/04/20 20:49

年毎の最大値を取得するのはこんな感じのSQLで良いのではないでしょうか？

SQLなのまず条件に合うSQLを１つ作り、そのSQLを元に発展させるだけなので難しく考えない方がいいですよ。

SELECT max(table1.nen) as 年度 , max(yyyy.yen) AS 金額
FROM table1 , (SELECT distinct YEAR(nen) as ynen , yen FROM table1 ) AS yyyy
where year(table1.nen) = yyyy.ynen
GROUP BY yyyy.ynen
ORDER BY yyyy.ynen;

この回答への補足

済みません。最初の質問のしかたが悪かったせいで皆さんを混乱させてしまったようです。改めて質問し直します。

日付と売上額で構成された店の売上テーブルがあります。このテーブルから各年ごとに売上額がもっとも多かった日とその売上額を抽出する方法が知りたいのですが。。。
よろしくお願いします。

日付 ,売上額
2003/04/10,550000
2003/04/11,210000
2003/04/12,975000
2004/07/01,515000
2004/07/02,189000
2004/07/03,884500
2004/09/12,445500
2004/09/13,150000

補足日時：2005/04/21 01:03

No.6 回答者： ngsvx 回答日時： 2005/04/20 16:24

で、結局、最大金額は年度だとして、日付というのは何を出せばいいのでしょうか？

その年度に含まれるすべての日付？

該当した年度を出力するというのなら話がわかるのですが。。。

No.5 回答者： iiikkk 回答日時： 2005/04/20 09:58

一度で取得する方法は思いつきませんが、

年ごとの最大金額を取得するView（年,金額）を作って
それと同じ物を全体から取得するようにしてはどうでしょうか。

DBがなにか分からないので、抽象的に書くと、

slect 日付,金額
from テーブル,View
where 日付(年だけ)=View.年 and 金額=View.金額
order by 日付

この回答への補足

申し訳ありません。ＤＢを書き忘れました。
ＤＢはAccess2002です。
よろしくお願いします。

補足日時：2005/04/20 11:16

No.4 回答者： taka451213 回答日時： 2005/04/20 09:55

こんにちは。

DBはなんですか？
それによって書き方が違う場合があります・・・。
本当に「年度ごと」での最大金額ですか？
日付ごとの間違いでは？？？
(^^ゞ

この回答への補足

ＤＢはAccess2002です。
最大金額は『年度ごと』です。
見本のテーブルで日付がダブっているのは間違えです。
紛らわしくて済みませんでした。

補足日時：2005/04/20 11:17

No.3 回答者： noname#11722 回答日時： 2005/04/20 09:36

またまたミスりました。

いやぁ自信なくすなぁｗ

Ｎｏ２ではムリです。
予期せぬレコードが出てきます。
金額で抽出してるので、他の年度の同金額まででるですな・・・

No.2 回答者： noname#11722 回答日時： 2005/04/20 09:25

間違えました。

日付と最大金額が欲しいのですね。

SELECT * FROM T_TABLE WHERE (金額 IN (SELECT MAX(金額) FROM T_TABLE GROUP BY YEAR(日付)))

でした。

No.1 回答者： noname#11722 回答日時： 2005/04/20 09:09

年度で良いですか？上のデータは全て2004年度ですが・・・

MS SQL Serverの場合

SELECT YEAR(日付) AS 年度, MAX(金額) AS 最大金額 FROM テーブル名 GROUP BY YEAR(日付)