複数のテーブルに跨る集計

質問

こんばんは。お世話になっております。

テーブル（member）
id　id_name　name　
1　takashi　さんま
2　sayuri　きゅうり
3　akemi　　とまと

テーブル（data）
id　date
2　07-1-20
3　07-1-20
2　07-1-20
1　07-1-21
2　07-1-21
3　07-1-21

以上のようなテーブルがあり、下記のように、テーブルdataのidをカウントし、多いもの順に結果を表示させたいのですが、このような場合のソースが分かりません。

結果
きゅうり　3件
とまと　　2件
さんま　　1件

似たような質問をされている方も過去に多くあるようですが、私のケースとどれが適しているかさえも分からず投函させていただきました。
お忙しい中恐縮ですが、説明のあるサイト、またはアドバイスなどご指導いただけたら幸いです。宜しくお願い致します。

回答No.2

asx · ベストアンサー

#1です。

1点、前回書き込んだSQL文に間違いがありましたね。
「from member M, date D」は「from member M, data D」の間違えでした。（二つ目のテーブル名はdateではなくてdataでしたね。）紛らわしい間違えをしてしまってすみません。。
こちらを修正してもうまくいきませんか？（SQL文の文法エラー？望んでいた結果が出ない？）

もし、表示する項目が、nameとカウント数以外にあるのでしたら、それも、group byで指定しないといけません。
例えば、idも表示する必要があるならば、
---------------------------------------------
select M.id, M.name, count(M.name) as count
from member M, data D
where M.id = D.id
group by M.name, M.id
order by count desc
---------------------------------------------
となります。
inner joinを使用するならば、
---------------------------------------------
select M.id, M.name, count(M.name) as count
from data D inner join member M on M.id = D.id
group by M.name, M.id
order by count desc
---------------------------------------------

> 見慣れないDやMの意味が分からずにいる状況です。
「テーブル名 別名」と書くことで、そのSQL文でだけ有効なテーブルの別名を付けることができます。
sadachaさんが書かれたSQL文に、
> FROM data INNER JOIN member ON data.id = member.id
とありますよね。
これを、dataテーブルを「D」、memberテーブルを「M」という別名を使った場合、
FROM data D INNER JOIN member M ON D.id = M.id
という書き方ができます。
テーブル名が長かったりしたときに、いちいち、「<テーブル名>.<項目名>」と書いているとそれだけでSQL文が長くなってしまって見辛くなってしまいますので、そういうときに使ったりします。
もちろん使わなきゃいけないわけでも、一般的に使うものでもなく、好みなので、別名をつけなくても問題ないですよ。
別名を使わないなら、最初に書いたSQL文は、
---------------------------------------------
select member.id, member.name, count(member.name) as count
from member, data
where member.id = data.id
group by member.name, member.id
order by count desc
---------------------------------------------
となります。

それから、
> WHERE category = '4001' OR category = '4002' OR category = '4003' OR category = '4004' OR category = '4005' OR category = '4006'
この部分ですが、「categoryが4001,4002,4003,4004,4005,4006のいずれかの場合」という条件指定ですよね。
これは、in句というものを使うことができます。
「項目名 in (値1,値2,値3）」と記述することで、項目名の値がが、値1か値2か値3の場合、という意味になります。
ですので、「category in (4001, 4002, 4003, 4004, 4005, 4006)」とすることができます。

うまくいかないようでしら、どんなエラーが表示されるか（もしくはどんな結果が出てしまうか）を教えてください。

お礼

asx様

早速のお返事を有難う御座います。
どれも詳細なご説明、本当に有難い気持ちで一杯です。非常に勉強になりました。

そこで2-3改めて質問なのですが、

結果
きゅうり　3件　1位
とまと　　2件　2位
さんま　　1件　3位

と、件数の取得（count）および、順位を表示させるのはどうしたらいいのでしょうか？
（名前は順に表示させることが出来ました！）

勉強不足とはいえ知らない事が多すぎると、改めて知らさせる思いで一杯ですが、引き続きアドバイス頂ければ幸いです。宜しくお願い致します。

補足

asx様

お世話になっております。
件数表示までは表示させる事ができたのですが、順位表示（1位、2位、…）までは未だ出来ずにおります。
色々と調べてはいるのですが、中には「出来ない」というような記載があったりで、「どうしたものか？」と頭を悩ませております。
引き続きアドバイス頂けたら幸いです。宜しくお願い致します。

回答No.3

yambejp

ランク付けについてはPHPを使っているなら基本的にはPHP側でやった方が
効率的だと思います。
どうしてもSQLでやるというなら以下のように変数を用意して加算していく
方法が妥当でしょう。この場合変数をインクリメントしていますので
SETを使って宣言しておかないといけません。
こんな感じです。

SET @RANK=0;
SELECT `M`.`id`
,`M`.`name`
,@RANK:=@RANK+1 AS `rank`
,COUNT(*) as `count`
FROM data AS `D`
INNER JOIN `member` AS `M` USING (`id`)
GROUP BY `M`.`id`
ORDER BY `count` DESC;

単純に@RANKを加算すると当然同点の場合でも順位がついてしまいます。
それが困る場合は上のレコードのカウント値を保存しておき、
オフセットしてやるとよいでしょう。
こんな感じです。

SET @RANK=0,@COUNT=0,@OFFSET=0;
SELECT `M`.`id`
,`M`.`name`
,(@RANK:=@RANK+1)+(@OFFSET:=IF(@COUNT=count(*),@OFFSET-1,0)) AS `rank`
,@COUNT:= COUNT(*) as `count`
FROM data AS `D`
INNER JOIN `member` AS `M` USING (`id`)
GROUP BY `M`.`id`
ORDER BY `count` DESC;

お礼

yambejp様

こんばんは。いつもお世話になっております。
ご挨拶が遅れて申し訳ありません。
後になって、当初の質問からズレテしまったこともあり、一旦「解決」とまで考えていましたが、yambejp様のいう『基本的にはPHP側で』ということに気付かされ、あれこれやっているうちにお返事が遅くなってしまいました。すみません。

時間は掛かってしまったものの、PHP側でやると、思いのほか簡単に出来るんですね。今回は（申し訳ないな・・と思いつつ）、PHPにて対処したいと思いますが、yambejp様からの貴重なアドバイスも今後に取り入れたいと思っております。
感謝しております。有難う御座いました！

回答No.1

asx

select M.name, count(M.name) as count
from member M, date D
where M.id = D.id
group by M.name
order by count desc

これでご希望の結果が出ると思いますよ。

テーブルの結合については判ることを前提にちょっとだけ解説しておきます。
----------
きゅうり　3件
とまと　　2件
さんま　　1件
----------
これは、つまり、名前毎に件数を表示させたいのですよね。
この「名前毎」というのがポイントです。
「○○毎」とまとめたいものを、group byで指定します。

表示順を変えたい場合は、order byを使用します。
order by 項目名
で使用することができますが、この場合は、指定した項目名の数字が小さい順に表示されます。
数字の大きい順に表示したい場合は、
order by 項目名 desc
と記述します。

Googleでgroup byやorder byで検索してみると、いろいろ出てくると思います。

お礼

asx様

はじめまして、こんばんば。早速の回答を有難う御座います！
ご丁寧に解説していただいているにも関わらず、お恥ずかしいながら上手く設定する事が出来ません。

当初、カウントすることが頭になかったときに書いたコードは下記のようなものですが、お教えいただいたコードに書き換えても、見慣れないDやMの意味が分からずにいる状況です。

お忙しい中恐縮ですが、今一度ご指導のほど宜しくお願い致します。
※質問時には省略していますが、検索結果の条件も記載しています。

$sql= "select * FROM data INNER JOIN member ON data.id = member.id WHERE category = '4001' OR category = '4002' OR category = '4003' OR category = '4004' OR category = '4005' OR category = '4006' limit 0,10 ";