下記のテーブルからエリア毎のfeeの上位2件を抜き出したい。

DBはmysqlです。

似たような質問や解説ページがたくさんありましたが、今ひとつ理解しきれませんでした。
今後の参考にしたいので、結果の出るsqlだけでなく、その詳細説明を教えいただけると幸いです。

TABLE
code | area | fee
1 | 大阪 | 400
2 | 広島 | 600
3 | 東京 | 700
4 | 東京 | 200
5 | 東京 | 600
6 | 大阪 | 600
7 | 大阪 | 500
8 | 広島 | 500
9 | 広島 | 100
10| 東京 | 200
11| 大阪 | 300
12| 広島 | 700


結果としては以下の様にfee順にarea2件ずつ取得したいのです。


2 | 広島 | 600
12| 広島 | 700
3 | 東京 | 700
5 | 東京 | 600
6 | 大阪 | 600
7 | 大阪 | 500

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2013/08/22 19:02:22
  • 終了:2013/08/29 19:05:08

ベストアンサー

id:tezcello No.3

tezcello回答回数459ベストアンサー獲得回数692013/08/27 20:26:46

ポイント33pt

別案という事で2回目の回答を。

SELECT *
  FROM tbl T1
  WHERE (SELECT COUNT(*)
     FROM tbl T2
     WHERE T2.area = T1.area
       AND T2.fee >= T1.fee
  ) < 3
  ORDER BY area DESC, fee DESC

副問合せの中で、同一areaかつ外側で注目しているfeeよりも高いのを数えています。(⇒fee の高い順にランキングをつけている)
その結果が、3より小さい(2以下としても同じでしょう)行を抽出して、areaと fee で並び替えています。


>> 追加 <<

2位が重複するとランキングが、1,3,3 となり、1件しか取得できません。
重複が無いとの事でしたが、表示されない値とはいえ気持ち悪いので...

SELECT *
  FROM tbl T1
  WHERE (SELECT COUNT(*)
     FROM tbl T2
     WHERE T2.area = T1.area
       AND T2.fee  > T1.fee
  ) < 2
  ORDER BY area DESC, fee DESC

ランキングは、0始まりとなります。

このSQLと、2位が重複すると重複しただけ取得できてしまいます。
CASE式で何とかなりそうな気がしますが、思い浮かびませんでした。

中途半端感が否めませんが、お好みの方を。

id:jamis

ありがとうございます。

目的の結果が導き出せました。
内容もよくわかりました。

2013/08/28 21:56:35

その他の回答(2件)

id:tezcello No.1

tezcello回答回数459ベストアンサー獲得回数692013/08/23 16:43:15

ポイント34pt
SELECT *
  FROM tbl T1
  WHERE T1.code IN 
    (SELECT T2.code FROM tbl T2
       WHERE T1.area = T2.area
       ORDER BY T2.fee DESC
       LIMIT 2
    )
;

こんな感じかなぁと。
一応手元の SQLite でテストはしました。
LIMIT は、他の RDBM を使う際は書式が違うかも。

実際は違うのでしょうが、ループ的に考えると...
T1 のある行に注目すると、副問合せ中の T1.area が決まりますから、その area での fee 降順リストから先頭2行の code を取り出して、T1.code が取り出した code リストに含まれていれば、求めている行だと。


少し追加します。

上のSQLで得られたものは、ご提示の順序とはなりません。
並べ替えが必要なら、ORDER BY を ; の前に付加する事になりますが、area を指定しても
広島→東京→大阪
の順序とはなりません。
__東京→広島→大阪(またはその逆)は可能です
__文字エンコードが異なると出来るかもしれませんが未確認です
__SQLite (つまり UTF-8) では順序を決める何らかの値が必要です

また、「以下の様にfee順に」とありますが、例示は「広島」は fee が昇順、他は降順となっています。
__area の件が解決できるなら、code の昇順で得られますが
__この例の場合に限りの話なので、無意味ですよね

他2件のコメントを見る
id:tezcello

SQLite で出来る事が MySQL で出来ないとは思いませんでした...

そのエラーメッセージでググると、解決方法の示唆が見つかりました。
「INで使えないならFROMで使う」って事の様です。
それに従って書き換えるとこんな感じです。
SELECT * FROM tbl T1
WHERE T1.code IN
(SELECT code FROM
(SELECT T2.code FROM tbl T2 WHERE T1.area = T2.area ORDER BY T2.fee DESC LIMIT 2 )
)
;

当方にも MySQL が無い訳ではないですけど、環境を整えるのが面倒なので未チェックです。

2013/08/24 17:59:36
id:jamis

ありがとうございます。

いろいろ調べながらやっているのですが、なかなかうまくいきません。
別の形でトライしてみます。

2013/08/27 01:56:42
id:Sampo No.2

Sampo回答回数556ベストアンサー獲得回数1042013/08/27 12:55:07

ポイント33pt

jamisさん、プログラマならば「上位2件を抜き出したい」という要望を受けたら「それだけじゃわかりません」と答えねばなりません。

  • 上位3件が同着だった場合はどう表示するのか(3件とも表示、それとも3件の中からランダムに2件だけ)?
  • 全部で1件しかなかった場合はどう表示するのか?

ここらへんの仕様を決めないと設計にはかかれません。
いかがでしょう?

それによって実装アイディアを提供できると思います。

他1件のコメントを見る
id:Sampo

feeは重複なしですね。ではさっそく、と思ったらまさにtezcelloさんの2回目の回答通りのものを答えるつもりでした。

なのでわざわざ別解を。

SELECT T1.code, T1.area, T1.fee
FROM tbl T1
LEFT JOIN tbl T2
ON T2.area = T1.area
AND T2.fee >= T1.fee
GROUP BY T1.code, T1.area, T1.fee
WHERE COUNT(T2.code) < 3
ORDER BY T1.area DESC, T1.fee DESC

ほぼ同じロジックをサブクエリではなく自己結合で実現しています。

2013/08/27 22:54:23
id:jamis

ありがとうございます。

これは複雑ですね。
目的に応じて、いろんな書き方ができると可能性が広がりますね。

2013/08/28 21:55:35
id:tezcello No.3

tezcello回答回数459ベストアンサー獲得回数692013/08/27 20:26:46ここでベストアンサー

ポイント33pt

別案という事で2回目の回答を。

SELECT *
  FROM tbl T1
  WHERE (SELECT COUNT(*)
     FROM tbl T2
     WHERE T2.area = T1.area
       AND T2.fee >= T1.fee
  ) < 3
  ORDER BY area DESC, fee DESC

副問合せの中で、同一areaかつ外側で注目しているfeeよりも高いのを数えています。(⇒fee の高い順にランキングをつけている)
その結果が、3より小さい(2以下としても同じでしょう)行を抽出して、areaと fee で並び替えています。


>> 追加 <<

2位が重複するとランキングが、1,3,3 となり、1件しか取得できません。
重複が無いとの事でしたが、表示されない値とはいえ気持ち悪いので...

SELECT *
  FROM tbl T1
  WHERE (SELECT COUNT(*)
     FROM tbl T2
     WHERE T2.area = T1.area
       AND T2.fee  > T1.fee
  ) < 2
  ORDER BY area DESC, fee DESC

ランキングは、0始まりとなります。

このSQLと、2位が重複すると重複しただけ取得できてしまいます。
CASE式で何とかなりそうな気がしますが、思い浮かびませんでした。

中途半端感が否めませんが、お好みの方を。

id:jamis

ありがとうございます。

目的の結果が導き出せました。
内容もよくわかりました。

2013/08/28 21:56:35

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません