こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

データの件数を集計してTOPを取得するためのSQL

データの件数を集計して、その結果からもっとも件数が多いものだけを抽出する SQL について教えてください。
例えば以下のようなテーブルがあります。

▼テーブル

名前 |交通手段|日付
-----+--------+----
Aさん|バス |5/1
Aさん|バス |5/2
Aさん|バス |5/3
Bさん|バス |5/1
Bさん|電車 |5/2

※「日付」については、本質問に直接の関係はありません。


このテーブルにクエリを発行して、以下の結果を取得したいと考えています。

▼取得したい結果 (ある交通手段をもっとも使った人を抽出)

名前 |交通手段|回数
-----+--------+----
Aさん|バス |3
Bさん|電車 |1

※回数が同数の人がいる場合は、「ユーザID」が一番若い人を採用します。
 「ユーザID」はこのテーブル例には含まれておりませんが、
 「名前」が「ユーザID」であると読み替えてください。



なお、以下の結果を取得するクエリは 質問番号:6719239 にご協力いただけたおかげで解決しました。

SELECT 名前, 交通手段, COUNT(*) AS 回数
FROM テーブル
GROUP BY 名前, 交通手段;
 ↓
名前 |交通手段|回数
-----+--------+----
Aさん|バス |3
Bさん|バス |1
Bさん|電車 |1

今回、この結果から 1件目 と 3件目 を抜き出したいと考えております。
プログラム側で対処はできるのですが、SQL の方がスマートに書けそうだと思っております。


なおレンタルサーバ上 (MySQL 5.0.77) で稼働させるため、
なるべく高速な SQL を希望いたします。

投稿日時 - 2011-05-07 13:01:54

QNo.6720114

すぐに回答ほしいです

質問者が選んだベストアンサー

訂正です。

select 名前,交通手段,件数
from ( select
@rnk:= if( @access<=>交通手段, @rnk+1, 1 ) as rnk,
名前,
@access:=交通手段 as 交通手段,
件数
from ( select
名前,
交通手段,
count(*) as 件数 from テーブル
group by 名前,交通手段
order by 交通手段 desc,件数 desc,名前
) T cross join
( select @access:=null, @rnk:=0 ) as dummy ) S
where rnk = 1
order by 名前,交通手段;

投稿日時 - 2011-05-07 17:19:58

お礼

誠にありがとうございます。

前質問がかなりスマートに書けてしまったことから
こちらもかなりスマートに書けるのかと思ってしまいましたが、やはり長くなってしまうのですね。
SQL は難しい。。。

ともあれ、本当にありがとうございました。
レスポンスが早かったので、とっかかりができて非常に助かりました。

実際のテーブルはもう少し複雑なため、このやり方を参考にしつつ進めております。

投稿日時 - 2011-05-08 00:47:09

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

ざっとフローどおりやるとこんな感じでいけます。
もう少し効率的な書き方がありそうな気もしますが・・・

SELECT MIN(名前) AS 名前,交通手段,回数
FROM(
SELECT 名前, 交通手段, COUNT(*) AS 回数
FROM テーブル
GROUP BY 名前, 交通手段
HAVING (交通手段,回数) IN(
SELECT 交通手段,MAX(回数)
FROM (
SELECT 交通手段, COUNT(*) AS 回数
FROM テーブル
GROUP BY 名前, 交通手段
) AS SUB
GROUP BY 交通手段
)
) AS SUB2
GROUP BY 交通手段,回数

投稿日時 - 2011-05-07 15:10:09

お礼

誠にありがとうございます。

前質問がかなりスマートに書けてしまったことから
こちらもかなりスマートに書けるのかと思ってしまいましたが、やはり長くなってしまうのですね。
SQL は難しい。。。

ともあれ、本当にありがとうございました。

ベストアンサーはレスポンスが早かった #1 の方に差し上げますが、こちらの方法も勉強になります。
実際のテーブルはもう少し複雑なため、このやり方を参考にしつつ進めます。

投稿日時 - 2011-05-08 00:45:20

ANo.1

select 名前,交通手段,件数
from ( select
@rnk:= if( @name<=>名前, @rnk+1, 1 ) as rnk,
@name:=名前 as 名前,
交通手段,
件数
from ( select
名前,
交通手段,
count(*) as 件数 from テーブル
group by 名前,交通手段
order by 名前 desc,交通手段 desc
) T cross join
( select @name:=null, @rnk:=0 ) as dummy ) S
where rnk = 1
order by 名前,交通手段

投稿日時 - 2011-05-07 14:29:27

あなたにオススメの質問