みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

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

解決済みの質問

mysqlでグループ内の最大値を取得する方法

はじめまして。

voteというテーブルにcategory_id、individual_id、timeという3つのフィールドがあります。

(1)category_idは名前の通りカテゴリーのIDで、0~100まであります。
(2)individual_idはcategory_idごとに0~100まであます。
(3)timeは行が挿入された時間が記入されています。投票されるたびに行が挿入されます。
(4)category_idとindividual_idとdateでユニークキーを構成しています。
(5)category_idとindividual_idの2つで、個人が特定されます。

select categry_id, individual_id, count(*) from vote group by category_id, individual_id
という命令文で、誰に何票voteされているかが分かります。

この先ですが、各カテゴリの中で最も多く投票されている3人ずつを表示したいのですが、どのようなSQLを書けばよいでしょうか。

似たような質問もあり、自分なりに四苦八苦して試してみたのですがうまくいきませんでした。
回答いただけると助かります。よろしくお願いいたします。

投稿日時 - 2013-01-30 22:52:36

QNo.7919858

すぐに回答ほしいです

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

4人以上を3人に絞り込むのは適当でいいとのことですので
投票数が同じ場合はユーザーのidが小さいものを上位とします
性質上一度集計をするためにviewをつくっておくとよいでしょう。

//テーブルをつくり
create table vote (cID int,iID int,cTime datetime,unique(cID,iID,CTime));

//ビューをつくります
create view v_vote as select cID,iID,count(*) as count from vote group by cID,iID;

//ダミーデータをいれます
insert ignore into vote values (1,1,'2013-01-01 00:00:00'),(1,1,'2013-01-01 00:00:01'),(1,1,'2013-01-01 00:00:02'),(1,1,'2013-01-01 00:00:03'),(1,1,'2013-01-01 00:00:04'),(1,1,'2013-01-01 00:00:05'),(1,2,'2013-01-01 00:00:06'),(1,2,'2013-01-01 00:00:07'),(1,2,'2013-01-01 00:00:08'),(1,3,'2013-01-01 00:00:09'),(1,3,'2013-01-01 00:00:10'),(1,4,'2013-01-01 00:00:11'),(1,4,'2013-01-01 00:00:12'),(1,4,'2013-01-01 00:00:13'),(2,1,'2013-01-01 00:00:14'),(2,2,'2013-01-01 00:00:15'),(2,3,'2013-01-01 00:00:16'),(2,4,'2013-01-01 00:00:17'),(2,5,'2013-01-01 00:00:18'),(2,1,'2013-01-01 00:00:19'),(3,2,'2013-01-01 00:00:20');

//まずビューを確認します
select * from v_vote;

//カテゴリごとにカウントの多い人順
select(
select count(*) +1
from v_vote AS v2
where (
v1.count=v2.count and v1.iID>v2.iID
or v1.count<v2.count
) and v1.cID=v2.cID
) AS rank
,cID,iID,count
from v_vote AS v1
having rank<=3
order by cID asc,rank asc;

投稿日時 - 2013-01-31 14:58:08

お礼

yambejpさんありがとうございます。
エラーも出ることなくうまく動きました。

ビューを遣うというテクニックは知りませんでした。
ありがとうございました。

投稿日時 - 2013-02-02 14:46:36

ANo.2

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

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

回答(2)

ANo.1

>最も多く投票されている3人ずつ

定義があいまい、上位3名という場合、同位のものがいた場合どうするのですか?
たとえば1位が10人いたら4人目以降をどうやって除外するつもりでしょう?
新しいtimeを持つ順、古いtimeを持つ順、individual_idの昇順・降順など
どれかかなぁとは思いますが・・・

あと、timeとかdateとか語句にぶれがあります。
実質datetimeのフィールドだとおもいますが、予約語をフィールド名に使うのは
混乱の元なので注意が必要です

投稿日時 - 2013-01-31 09:34:13

補足

yambejpさん、コメントありがとうございます。
timeはcurrent_timestampで取得した値となっています。

なお10人同順位となった場合ですが、特にこだわらず10人中3人が選出できれば問題ないです。
例えばorder by count(*) desc limit 0, 3でmysqlが取得する値そのもので大丈夫です。

よろしくお願いいたします。

投稿日時 - 2013-01-31 10:49:05

あなたにオススメの質問