複数行の上位3位を1行に表示

質問

複数行の上位3位を1行に表示する。

分かる方がいれば教えて下さい。
OS：Windows7
DB：PostgresSQL 8.3

＜参照デーブル＞
品番　　ID　　　金額　　　　日付
1;　　　　3;　　　1000000;　"2012-08-31"
1;　　　　2;　　　970000;　　"2012-08-18"
1;　　　　1;　　　970000;　　"2012-08-10"
1;　　　　4;　　　940000;　　"2012-08-20"
2;　　　　16;　　300000;　　"2012-08-25"
2;　　　　13;　　300000;　　"2012-08-13"
2;　　　　12;　　250000;　　"2012-08-10"
2;　　　　15;　　250000;　　"2012-08-18"
2;　　　　14;　　200000;　　"2012-08-15"
2;　　　　11;　　200000;　　"2012-08-08"

上記の様なテーブルを、金額が高くかつ時間の速いもの順で、上位3位を1行に表示したいのですが、上手くいかずに困っています。

＜抽出したい結果＞
品番　　ID1　　　金額1　　　　ID2　　金額2　　　ID3　　　金額3
1;　　　　3;　　　　1000000;　　1;　　　970000;　　　2;　　　970000
2;　　　　13;　　　　300000;　　16;　　300000;　　　12;　　　250000

このように抽出しいたいのですが上手くいきません。
どなたか教えていただけいないでしょうか。
よろしくお願いします。

回答No.1

nda23 · ベストアンサー

SQLは論理的に考えないとできません。
先ずIDは一意だと仮定します。
品番毎に順位を付けるとすると、以下の
理論になります。
(1)品番が同じである。
(2)あるレコードから見て、自分より金額が大きいか、
　金額が等しく日付が小さいレコード数を数える。
(3)上記で数えた数値が0なら自分が1位、1なら2位
　2なら3位である。
ということで、順位を見つけるクエリがこちら

SELECT A.品番,A.ID,MAX(A.金額) 金額Z
FROM 参照テーブル A LEFT JOIN 参照テーブル B
ON A.品番=B.品番 AND (A.金額<B.金額 OR
(A.金額=B.金額 AND A.日付>B.日付))
GROUP BY A.品番,A.ID
ORDER BY A.品番,COUNT(B.ID)

1位のレコードなら以下の条件にすればよい。
HAVING COUNT(B.ID)=0
よって、COUNT(B.ID)が0、1、2のものを抽出する
クエリを品番で結合して並べればよいのです。

SELECT A.品番,A.ID,A.金額Z,B.ID,B.金額Z,C.ID,C.金額Z
FROM ((SELECT A.品番,A.ID,MAX(A.金額) 金額Z FROM
参照テーブル A LEFT JOIN 参照テーブル B
ON A.品番=B.品番 AND (A.金額<B.金額 OR
(A.金額=B.金額 AND A.日付>B.日付))
GROUP BY A.品番,A.ID HAVING COUNT(B.ID)=0) A
LEFT JOIN
(SELECT A.品番,A.ID,MAX(A.金額) 金額Z FROM
参照テーブル A LEFT JOIN 参照テーブル B
ON A.品番=B.品番 AND (A.金額<B.金額 OR
(A.金額=B.金額 AND A.日付>B.日付))
GROUP BY A.品番,A.ID
HAVING COUNT(B.ID)=1) B ON A.品番=B.品番)
LEFT JOIN
(SELECT A.品番,A.ID,MAX(A.金額) 金額Z FROM
参照テーブル A LEFT JOIN 参照テーブル B
ON A.品番=B.品番 AND (A.金額<B.金額 OR
(A.金額=B.金額 AND A.日付>B.日付))
GROUP BY A.品番,A.ID
HAVING COUNT(B.ID)=2) C ON B.品番=C.品番
ORDER BY A.品番

PostgreSQLなので、英大文字や漢字のフィールドの
名前は""で囲まないとダメかも知れません。そこは
臨機応変にやってください。
★金額も日付も同じものは無いという前提です。
仮にこれがあると、1位のレコードが2個になり、
COUNT(B.ID)=0のデータは無くなります。
この場合は結合条件に金額と日付が同じならIDの
小さい方を数えるように条件を追加してください。

お礼

ご丁寧な回答ありがとうございました。
教えて頂いた方法で上手く抽出することができました。

金額も日付も同じテーブルが存在した場合についても教えて頂いたように、条件を追加して抽出できました。

＜追加する条件＞
OR (A.金額=B.金額 AND A.日付=B.日付 AND A.ID > B.ID)

＜上記を追加した結合条件＞
(SELECT A.品番,A.ID,MAX(A.金額) 金額Z FROM
参照テーブル A LEFT JOIN 参照テーブル B
ON A.品番=B.品番 AND (A.金額<B.金額 OR
(A.金額=B.金額 AND A.日付>B.日付)　
OR (A.金額=B.金額 AND A.日付=B.日付 AND A.ID > B.ID))

こんな感じでしょうか・・・。