• ベストアンサー
  • すぐに回答を!

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を書けばよいでしょうか。 似たような質問もあり、自分なりに四苦八苦して試してみたのですがうまくいきませんでした。 回答いただけると助かります。よろしくお願いいたします。

共感・応援の気持ちを伝えよう!

  • 回答数2
  • 閲覧数220
  • ありがとう数1

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

  • ベストアンサー
  • 回答No.2
  • yambejp
  • ベストアンサー率51% (3827/7415)

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;

共感・感謝の気持ちを伝えよう!

質問者からのお礼

yambejpさんありがとうございます。 エラーも出ることなくうまく動きました。 ビューを遣うというテクニックは知りませんでした。 ありがとうございました。

関連するQ&A

  • 1個のSQL分で2種類以上の件数値を取得する

    SQL分の記述で質問なんですが、 データベース上に日付、時間、フラグと言う項目があります。 同一の日付、時間のものは数件ずつあります。 フラグは"b"と"1"があります。 このデータの日付・時間辺りの件数と+フラグが"1"の件数を同時に取得するSQL分はかけますでしょうか? 別々ならば、 SELECT DATE,TIME,COUNT(*) AS KENSU1 FROM W_TABLE GROUP BY DATE,TIME ORDER BY DATE,TIME と SELECT DATE,TIME,COUNT(*) AS KENSU2 FROM W_TABLE WHERE FLG="1" GROUP BY DATE,TIME ORDER BY DATE,TIME でかけると思うのですが、 1個のSQL分で記述は可能でしょうか? 処理結果を 05/02/23 12:00 10 5 05/02/24 10:00 12 3 (日付・時間・件数・フラグ="1"の件数  見たいに取得したいのですが..

  • Accessで最小値と最大値を一度で取得する方法

    OS:WinXP Access:2000 下記の状態にしたいのですが よいSQL文が思いつきません。 MINのみのデータを抽出する方法は分かるのですが select ID,Name,Min(Date) from tblA group by ID,Name,Date MAX(Date)も出力しようとすると、Dateが最小のNameの出力方法が分かりません。 テーブル(tblA) ID|Name|Date --------------- 01|C |8/10 01|A |8/11 01|B |8/12 ↓ クエリ実行後 ID|MIN(Name)|MIN(Date)|MAX(Date) ----------------------------------- 01|C |8/10 |8/12 よろしくお願いいたします。

  • グループ毎にある列の最大値のレコードを取得する方法

    Accessで下記のようなテーブルでkeyフィールド毎のdayフィールドの最大値のレコードを取得したいと思ったのですが、keyフィールドとdayフィールドのみを表示する分には下記の記述で大丈夫かと思ったのですが、これにidフィールドとfieldフィールドもあわせて表示したい場合、どのように記述すれば良いか分からなかった為、アドバイスいただける方がいらっしゃいましたら、ご教示の程よろしくお願いします。 なお、出来れば同じkey,同じdayが複数あった場合、一番大きいid(主キー)のレコードを取得したいと思っています。 【テーブル(T_TBL)】 id|key|day|field ------------------ 1|1|     |100 2|2|2011/03/04|400 3|2|2011/03/03|500 4|3|2011/03/10|300 5|3|2011/03/10|200 【下記ソース実行時のメッセージボックスの表示結果】 1 : 2 : 2011/03/04 3 : 2011/03/10 【希望する表示結果】 1 : 1 : : 100 2 : 2 : 2011/03/04 : 400 5 : 3 : 2011/03/10 : 200 【ソース】 Option Compare Database Option Explicit Private Sub Form_Load() Dim db As DAO.Database Dim rs As DAO.Recordset Dim SQL As String Dim strMsg As String Set db = CurrentDb() SQL = "" SQL = SQL & "SELECT key, Max(day) AS max_day" SQL = SQL & " FROM T_TBL" SQL = SQL & " GROUP BY key" Set rs = db.OpenRecordset(SQL) Do Until rs.EOF strMsg = strMsg & vbNewLine & rs!Key & " : " & rs!max_day rs.MoveNext Loop MsgBox vbNewLine & strMsg rs.Close: Set rs = Nothing db.Close: Set db = Nothing End Sub 以上、よろしくお願いします。

その他の回答 (1)

  • 回答No.1
  • yambejp
  • ベストアンサー率51% (3827/7415)

>最も多く投票されている3人ずつ 定義があいまい、上位3名という場合、同位のものがいた場合どうするのですか? たとえば1位が10人いたら4人目以降をどうやって除外するつもりでしょう? 新しいtimeを持つ順、古いtimeを持つ順、individual_idの昇順・降順など どれかかなぁとは思いますが・・・ あと、timeとかdateとか語句にぶれがあります。 実質datetimeのフィールドだとおもいますが、予約語をフィールド名に使うのは 混乱の元なので注意が必要です

共感・感謝の気持ちを伝えよう!

質問者からの補足

yambejpさん、コメントありがとうございます。 timeはcurrent_timestampで取得した値となっています。 なお10人同順位となった場合ですが、特にこだわらず10人中3人が選出できれば問題ないです。 例えばorder by count(*) desc limit 0, 3でmysqlが取得する値そのもので大丈夫です。 よろしくお願いいたします。

関連するQ&A

  • 2つのテーブルからグループ別けして集計

    こんばんは。お世話になっております。 質問のタイトルが少し変かもしれませんが、2つのテーブルがあり、 1つはカテゴリ名登録用。1つはカテゴリ別詳細データといった感じのテーブルがあるのですが、データの集計をする際、コンテンツ毎に登録された数を表示させたいのですが、登録のないデータは表示できない状態となっています。 video_category(カテゴリ登録用テーブル) cate_id  category_name 1     ハリーポッター 2     ポケモン 3     ドラえもん video(カテゴリ別詳細テーブル) no  category  title 1   1    不死鳥の騎士団    2   1    賢者の石 ※categoryは上記テーブルのcate_idと紐づけている このようなデータの場合、 ハリーポッター(2) ポケモン(0) ドラえもん(0) としたいのですが、 $sql = "select SV.`no`, SV.`category`, SV.`title`, SV.`contents`, SV.`image`, VC.`cate_id`, VC.`category_name`, count(VC.`cate_id`) as `count` from `video_category` AS VC right join `video` AS SV on SV.`category` = VC.`cate_id` group by VC.`cate_id` order by VC.`cate_id`"; だと、0値のデータが反映されません。 お恥ずかしい質問かと思いますが、ご指摘いただけると幸いです。

    • ベストアンサー
    • MySQL
  • グループ化したSQLの書き方について

    id,stpass,tokuten,branch,dateというフィールドをもつresultというテーブルを作成しました。 最高得点(フィールド名:tokuten)は、下記SQL(クエリーで作りました)でできます。 SELECT result.id, result.stpass, Max(result.tokuten) AS tokutenの最大 FROM result GROUP BY result.id, result.stpass; ただ、最高得点(Max(result.tokuten) AS tokutenの最大)を出したと同じレコードのそれ以外のフィールド(branch,date) を表示させるSQL(クエリー)がわかりません。 何方か、ご存知の方おられましたら、ご教授ください。

  • SQL文作成のお願い

    前提として下記のようなテーブルがあります。 テーブル名:test フィールド: 1)id: varchar, not null 2)name: varchar 3)date: timestamp with time zone, not null このテーブルに下記のようなレコードがあります。 '00001', 'A', '2005-01-01 00:00:00' '00001', 'B', '2005-07-01 00:00:00' '00001', 'C', '2005-11-01 00:00:00' '00002', 'X', '2005-01-20 00:00:00' '00002', 'Y', '2005-07-20 00:00:00' '00002', 'Z', '2005-11-20 00:00:00' ■今やりたいこと このテーブルからidごとにdateフィールド値が最近のレコードを取得したい。 ■やってみたこと 下記SQLを発行しました。 >select id, max(date) from test group by id order by id 結果==> 00001 2005-11-01 00:00:00 00002 2005-11-20 00:00:00 実際はnameフィールドの値も取得したいのですが、group by句を使うとフィールド指定ができませんでした。 ■だめだったSQL select id, name, max(date) from test group by id order by id どのようにSQLを書けばよろしいでしょうか?

  • ソートしてからグループ化したいけれど・・・。

    SQL構文について質問させて下さい。 ABC │ID │DATE ------------------ XXX │aaa │02/01 YYY │bbb │02/02 YYY │aaa │02/03 と言うデータから、DATEが新しい(大きい)値を抽出し、 次に重複するIDを排除した結果を出したいです。 希望の検索結果は、 ABC │ID │DATE ------------------ YYY │bbb │02/02 YYY │aaa │02/03 となって欲しいのです。 単純にソートした後でIDでグループ化したいのですが、当然できなく、私の考えでは、 SELECT abc, id, max( date ) FROM `test` GROUP BY id ORDER BY date DESC なのですが結果は、 ABC │ID │DATE ------------------ YYY │bbb │02/02 XXX │aaa │02/03 と言う風になり、何故か、2行目の様にDATEに対して、ABCの値が別の行の ID:aaaの値になってしまってます。 どうしたら良いのでしょうか?

    • ベストアンサー
    • MySQL
  • 一定以上の行があれば最大値を持つ行を削除するSQL

    MySQL5にて、あるテーブルの特定フィールドの数(つまりcount値)が一定値以上であれば、間引きのため別のフィールドの値で最大値をもつ行を削除するSQLを考案中です。 イメージとしては以下の通りですが、SQLとしては誤っており、また、冗長です。 DELETE FROM table WHERE id={$id} AND (SELECT COUNT(*) FROM table WHERE id={$id}) > 5 AND begin = (SELECT MAX(begin) FROM table WHERE id={$id}); よい知恵をお授けください。

    • ベストアンサー
    • MySQL
  • MySQLにてCOUNTした値を更にCOUNT

    MySQLにてCOUNTした値を、更にCOUNTする事を1回のSQLにて実行する事は可能でしょうか? ※2回のSQL、プログラミングを介する、EXCELで再集計する等でならCOUNTのCOUNTは求められるのですが…1回のSQLでの回答お願いします。 ユーザーIDと課金額のカラムからなる、以下のようなテーブルがあるとします。 buyテーブル user_id,coin 1,100 5,100 10,200 5,200 5,200 17,100 1,100 2,200 23,500 ユーザー毎の課金回数は下記にて求められますが、 SELECT user_id,COUNT(coin) FROM buy GROUP BY id user_id,count(coin) 1,2 2,1 5,3 10,1 17,1 23,1 count(coin)毎の人数というのはどのように求めればいいでしょうか? いわば下記のような集計結果が理想です。 ※このような結果を1回のSQLにて出力するSQLをズバリ今回の例に沿って知りたいです。 count(coin),count(count(coin)) 1,4 2,1 3,1 ■環境 Apache2 MySQL5 PHP5 ご教授よろしくお願いします。

    • ベストアンサー
    • MySQL
  • MySQLでの複数テーブル(4つ)への検索について

    はじめまして、こんばんは。 現在、ECサイトを構築中なのですが、MySQLにPHPから検索をかけたいのですが、どうにも詰まってしまったので、投稿させて頂きました。 PHPバージョン:PHP 5.2.6 DBバージョン:MySQL 4.1.22 機能としては、購入履歴から、購入商品のランキングを作りたいと思っております。 ランキング自体の表示はOKなのですが、そこに商品のカテゴリーを表示させたいのです。 関係するテーブルは以下の通りです。 【order】購入履歴テーブル product_id:商品ID 【products】商品テーブル product_id:商品ID name:商品名 image:商品画像 【p_category】商品カテゴリーテーブル product_id:商品ID category_id:カテゴリーID 【category】カテゴリーテーブル category_id:カテゴリーID category_name:カテゴリー名 parent_category_id:親カテゴリーID 【表示させたい項目】 product_id:商品ID name:商品名 image:商品画像 category_id:カテゴリーID category_name:カテゴリー名 parent_category_id:親カテゴリーID 現在のソースは以下の通りです。 --------------------------------------- SELECT   count(o.product_id) as rank,   name,   image,   p.product_id FROM   order as o,   products as p WHERE   o.product_id=p.product_id GROUP BY   name,   image,   p.product_id ORDER BY   rank desc limit 5 --------------------------------------- 上記にプラスしたいのは、 p.product_idと【p_category】のproduct_idが一致したcategory_idを取得して、【p_category】のcategory_idと【category】category_idが一致したcategory_name、parent_category_idも取得したいです。 分かりにくいうえに、長文になってしまいましたが、ご指導・ご鞭撻のほど宜しくお願い申し上げます。

    • ベストアンサー
    • MySQL
  • グループ中で最大値を含む行を抽出するSQLはどう書けば良いでしょうか

    列A,Bでグループ化し、グループ中で列Cが最大値の列を取得したいのです。 数百万件あるので極力低コストにしたく、 副問合せはINDEXが効かないと聞いたのですが、 それ以外思いつきませんでした。 副問合せしない方法はないでしょうか? また、副問合せの中でコストの低いSQLはどんなものでしょうか。 ■テーブルです 名前 型 CUST_ID CHAR(8) BRANCH_ID CHAR(4) VALID_DATE DATE MYDATA VARCHAR2(20) CUST_ID BRAN VALID_DA MYDATA 19740704 0000 06-01-01 comment0001 19740704 0000 05-12-01 comment0000 19740704 0000 06-02-01 comment0002 19740704 0001 06-01-01 comment0011 19740704 0001 06-03-01 comment0012 ■このような結果が欲しいのですが。。。 CUST_ID BRAN VALID_DA MYDATA 19740704 0000 06-02-01 comment0002 19740704 0001 06-03-01 comment0012 ■考えたSQLです(検証済) select a.cust_id,a.branch_id,a.valid_date,a.mydata from test a,(select cust_id,branch_id,max(valid_date) as mvd from test group by cust_id,branch_id) b where a.cust_id=b.cust_id and a.branch_id=b.branch_id and a.valid_date=b.mvd ; select a.cust_id,a.branch_id,a.valid_date,a.mydata from test a where not exists (select * from test b where a.cust_id=b.cust_id and a.branch_id < b.branch_id) ; select cust_id,branch_id,valid_date,mydata from test where (cust_id,branch_id,valid_date) in (select cust_id,branch_id,max(valid_date) from test group by cust_id,branch_id) ; select cust_id,branch_id,valid_date,mydata from (select cust_id,branch_id,valid_date,mydata,max(valid_date) over(partition by cust_id,branch_id) as mvd from test) where valid_date=mvd ;

  • MYSQLで重複せずにカウント上位から取得したい

    お世話になります。 MYSQLにて以下のようなtableがあるとします。 id ex_id name count flag time memo 1 1 山田 100 on 10:11 テスト 2 1 山田 80 on 10:12 テスト2 3 2 田中 50 on 10:13 テスト3 4 3 鈴木 30 on 10:14 テスト4 5 3 鈴木 110 on 10:14 テスト5 6 3 鈴木 10 on 10:14 テスト6 このうち、countの数字が高いものから表示したいのですが、 ex_idが重複する場合はその数字が最も高いもののみ表示したく考えています。 目的とした表示結果順は以下です。 id ex_id name count flag time memo 5 3 鈴木 110 on 10:14 テスト5 1 1 山田 100 on 10:11 テスト 3 2 田中 50 on 10:13 テスト3 distinctやgroup byなど色々考えてみたのですが、思ったような挙動になりませんでした。 以上、よろしくお願い致します。

  • mysqlでSELECTの速度を上げる方法

    以下のようなSQLを発行すると、mysqlの処理時間が非常に多くかかるため、なんとか最適化を行いたいと考えています。 どのような方法があるのか教えていただけませんしょうか。 SELECT user_id,comment,comment_id,date,study_time,study,source FROM data_temp t1 WHERE NOT EXISTS (select comment_id from data t2 where t1.comment_id = t2.comment_id) ■補足 ・dataとdata_tempのテーブル構造は全く同じです。 ・SQLで実現したいことは、両テーブルのcomment_idをキーとして、dataに含まれないdata_tempの差分データを表示させたい。 なお、以下のインデックス作成は行いましたが、結果変わらずでした。 alter table data t1 ADD INDEX_t1 (user_id,comment,comment_id,date,study_time,study,source); alter table data_temp t1 ADD INDEX_t1 (user_id,comment,comment_id,date,study_time,study,source); よろしくお願いします。

    • ベストアンサー
    • MySQL