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
No.3
460
69
33pt
別案という事で２回目の回答を。
55
53
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 の高い順にランキングをつけている）
その結果が、３より小さい（２以下としても同じでしょう）行を抽出して、areaと fee で並び替えています。
＞＞ 追加 ＜＜
２位が重複するとランキングが、1,3,3 となり、１件しか取得できません。
重複が無いとの事でしたが、表示されない値とはいえ気持ち悪いので...
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
ランキングは、０始まりとなります。
このＳＱＬと、２位が重複すると重複しただけ取得できてしまいます。
CASE式で何とかなりそうな気がしますが、思い浮かびませんでした。
中途半端感が否めませんが、お好みの方を。
No.1
46069
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 降順リストから先頭２行の code を取り出して、T1.code が取り出した code リストに含まれていれば、求めている行だと。
少し追加します。
上のＳＱＬで得られたものは、ご提示の順序とはなりません。
並べ替えが必要なら、ORDER BY を ; の前に付加する事になりますが、area を指定しても
広島→東京→大阪
の順序とはなりません。
＿＿東京→広島→大阪（またはその逆）は可能です
＿＿文字エンコードが異なると出来るかもしれませんが未確認です
＿＿SQLite (つまり UTF-8) では順序を決める何らかの値が必要です
また、「以下の様にfee順に」とありますが、例示は「広島」は fee が昇順、他は降順となっています。
＿＿area の件が解決できるなら、code の昇順で得られますが
＿＿この例の場合に限りの話なので、無意味ですよね
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 が無い訳ではないですけど、環境を整えるのが面倒なので未チェックです。
ありがとうございます。
いろいろ調べながらやっているのですが、なかなかうまくいきません。
別の形でトライしてみます。
No.2
556104
33pt
jamisさん、プログラマならば「上位2件を抜き出したい」という要望を受けたら「それだけじゃわかりません」と答えねばなりません。
ここらへんの仕様を決めないと設計にはかかれません。
いかがでしょう？
それによって実装アイディアを提供できると思います。
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
ほぼ同じロジックをサブクエリではなく自己結合で実現しています。
ありがとうございます。
これは複雑ですね。
目的に応じて、いろんな書き方ができると可能性が広がりますね。
No.3
46069
ここでベストアンサー
33pt
別案という事で２回目の回答を。
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 の高い順にランキングをつけている）
その結果が、３より小さい（２以下としても同じでしょう）行を抽出して、areaと fee で並び替えています。
＞＞ 追加 ＜＜
２位が重複するとランキングが、1,3,3 となり、１件しか取得できません。
重複が無いとの事でしたが、表示されない値とはいえ気持ち悪いので...
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
ランキングは、０始まりとなります。
このＳＱＬと、２位が重複すると重複しただけ取得できてしまいます。
CASE式で何とかなりそうな気がしますが、思い浮かびませんでした。
中途半端感が否めませんが、お好みの方を。
ありがとうございます。
目的の結果が導き出せました。
内容もよくわかりました。
1
1
2
2
1
1
5
ありがとうございます。
2013/08/28 21:56:35目的の結果が導き出せました。
内容もよくわかりました。