人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

下記のテーブルからエリア毎の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

●質問者: jamis
●カテゴリ:コンピュータ ウェブ制作
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● tezcello
●34ポイント
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 の昇順で得られますが
__この例の場合に限りの話なので、無意味ですよね


jamisさんのコメント
回答ありがとうございます。 fee順の広島は単純にこちらの表示ミスです。単純に大きい数字から小さい数字を並べるだけなので、ORDER BYで問題ないです。エリアに関しても。 当方、mysqlを使用しております。 チェックしてみたのですが、サブクエリにはLIMITは使えないとのエラーが出てきました。

jamisさんのコメント
このようなエラーメッセージが返ってきます。 #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' 訂正しますと、このような形で問題ありません。 3 | 東京 | 700 5 | 東京 | 600 12| 広島 | 700 2 | 広島 | 600 6 | 大阪 | 600 7 | 大阪 | 500

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 が無い訳ではないですけど、環境を整えるのが面倒なので未チェックです。

jamisさんのコメント
ありがとうございます。 いろいろ調べながらやっているのですが、なかなかうまくいきません。 別の形でトライしてみます。

2 ● Sampo
●33ポイント

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

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

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


jamisさんのコメント
ありがとうございます。 最終的な結果として下記のテーブルを結果として算出したいと考えています。 【結果】 code | area | fee 3 | 東京 | 700 5 | 東京 | 600 12| 広島 | 700 2 | 広島 | 600 6 | 大阪 | 600 7 | 大阪 | 500 重複などは無いという前提なので、上位3件同数値、全部で1件など諸条件を加味する必要はありません。 質問欄のテーブルを結果の形に、つまり、エリア毎のfeeの上位2件を抜き出したいのです。

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 ほぼ同じロジックをサブクエリではなく自己結合で実現しています。

jamisさんのコメント
ありがとうございます。 これは複雑ですね。 目的に応じて、いろんな書き方ができると可能性が広がりますね。

3 ● tezcello
●33ポイント ベストアンサー

別案という事で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式で何とかなりそうな気がしますが、思い浮かびませんでした。

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


jamisさんのコメント
ありがとうございます。 目的の結果が導き出せました。 内容もよくわかりました。
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ