人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

SQLの質問です。MySQL5です。

Table_A
・book_id
・title

Table_B
・book_id
・tag

上記2つのテーブルがあります。2つのテーブルはbook_idで関連付けされています。
Table_Bはユーザーからの投稿データ(本に対する「タグ」)を蓄積しています。

book_idが200の本に対して以下のタグが付けられたとします。

面白い
面白い
つまらない
高い

最も多く投稿されたタグを取り出す(上の例だと面白い)場合は

SELECT tag FROM Table_A INNER JOIN Table_B USING(book_id)
WHERE Table_A.book_id = 200 GROUP BY Table_B.tag ORDER BY count(Table_B.tag) desc limit 1");

と書けました。


それでは以下の場合はどうすればいいでしょうか。

***最も多く投稿されたタグが「面白い」本を全て抽出***


検索機能に上記SQLを使いたいのです。MySQL5なのでサブクエリが使えますが、サブクエリを勉強した事が無いので使い方がわかりません。

みなさんよろしくお願いします。

●質問者: tokyosmash
●カテゴリ:インターネット ウェブ制作
✍キーワード:.book GROUP SELECT SQL tag
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● b-wind
●23ポイント

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 6.4.2.3 ANY、IN、SOME とともに使用したサブクエリ

SELECT book_id,title FROM Table_A AS a
 WHERE ( SELECT count(*) FROM Table_B AS b where a.book_id = b.book_id AND tab = '面白い' )
 >= ANY ( SELECT count(*) FROM Table_B AS b where a.book_id = b.book_id GROUP BY tag );

うーん、工夫すればもっと効率よく出来そうな…。

◎質問者からの返答

うーんこれはとても複雑ですね。

リファレンス見ながら勉強してみますけど時間がかかりそうです。

ありがとうございます。


2 ● kurukuru-neko
●23ポイント

>最も多く投稿されたタグが「面白い」本を全て抽出

1. それぞれの本の投稿されたタグの

数を数える。

SQLにすると

select book_id,tag,count(tag) from Table_B

group by tag;

2.もっとも多く投稿されたものを抽出する

select book_id,tag,count(tag) from Table_B

group by tag order by count(tag) desc limit 1;

3. タグが「面白い」をのみを抽出

select a.book_id,a.title

from Table_A as a

inner join

(select book_id,tag,count(tag)

from Table_B as b

group by tag

order by count(tag)

desc limit 1

) as t

on a.book_id = t.book_id and b.tag = '面白い'

◎質問者からの返答

コメント欄で大量の情報ありがとうございました。たまたまMySQL5を使っていたので「トリガ」に挑戦してみようと思います。クエリした結果をさらにクエリして・・という処理は現時点で自力で書くのは非常に効率が悪いです。みなさんのSQL文を見て理解するのも厳しいですから。。


3 ● Yota
●22ポイント

これでどうでしょう。

SELECT shukei.book_id, MAX(shukei.kaisu), shukei.tag FROM (SELECT book_id,tag,COUNT(tag) AS kaisu FROM Table_B GROUP BY book_id,tag ) AS shukei GROUP BY shukei.book_id HAVING shukei.tag='おもしろい' ;

1.内側のクエリでbook_idごとtagごとの集計をする。

SELECT book_id,tag,COUNT(tag) AS kaisu FROM Table_B GROUP BY book_id,tag ;

ここでできたテーブルの名前をshukeiとする。

2.book_idごとにグループ化して、kaisuが最大のものでtagおもしろいだけ抽出する。

◎質問者からの返答

ありがとうございます。

今回はなんとか成功しましたが、このような処理を毎回自力で書くのは現時点では厳しそうです。

オライリーのSQLクックブックに細かい事例が載っていたので買ってきました。今から勉強します。回答ありがとうございました。


4 ●
●22ポイント
SELECT good_book.book_id, a.title
FROM (
 SELECT t1.book_id
 FROM (
--
-- book_id ごとのタグの最大値を取得
-- t1 テーブルとする
--
 SELECT book_id, max(tag_count) AS tag_max
 FROM (
 SELECT book_id, tag, count( tag ) AS tag_count
 FROM Table_B
 GROUP BY book_id, tag
 ) tmp
 GROUP BY book_id
 ) AS t1 INNER JOIN (
--
-- book_id、tag ごとのタグ数を取得
-- t2 テーブルとする
--
 SELECT book_id, tag, count( tag ) AS tag_count
 FROM Table_B
 GROUP BY book_id, tag
 ) AS t2
--
-- t1 と t2 を結合して、book_id ごとにタグの最大値を持つ行を取得
-- その中でタグが '面白い' もののみを抽出
-- つまり、面白いの数がタグの中でもっとも多いものを抽出
-- (SELECT句では t1.book_id のみ選択)
-- good_book テーブルとする
--
 ON t1.book_id = t2.book_id AND t1.tag_max = t2.tag_count
 WHERE t2.tag = '面白い'
) good_book
--
-- good_book テーブルと Table_A を結合して title を取得
--
INNER JOIN Table_A As a
ON good_book.book_id = a.book_id
;

パフォーマンスはあやしいですが、「面白い」タグの数が同率1位でも正しく取得できるはずです。

参考になれば幸いです。

◎質問者からの返答

回答ありがとうございます。

とても見やすくて助かります。

ただ理解はできるのですが、ちょっと応用させて自分でSQL文を書くとなると非常に時間がかかります。質問文ではテーブルが2つですが、本当は3つのテーブルなんですね。それを結合させた上でクエリを2回やって・・となるともう複雑すぎます。

今回の処理であれば、事前にトリガーでそれぞれの本ごとに一番投稿されたタグを計算させておくのが簡単だと気付きました。

せっかくMySQL5を使っているので新しい機能を試してみようと思います。情報が少なくて困っていますが・・

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ