27914


MySQLデータベースに、記事テーブル posts とタグを表す tags、中間テーブルの post_tag の３つのテーブルが有ります。  
記事には複数のタグを持ち、タグは複数の記事に属します。  
post_tag は post_id, tag_id というカラムでそれぞれをつないでいます。

タグ 1, 2, 3 で検索した時、３つのタグをすべて持っている記事は 3、一致するタグが２つだけの時は 2
となるようなクエリはどのように書いたら良いのでしょうか？

参考までに現状の３つのタグのいずれかを含む記事を得るクエリを掲載しておきます。

[code]

    SELECT posts.* FROM posts
    JOIN post_tag ON posts.id = post_tag.post_id
    JOIN tags ON post_tag.tag_id = tags.id
    WHERE tags.id IN(1, 2, 3)
    GROUP BY posts.id
    
[/code]

MySQLのバージョンは 5.5 です。よろしくお願いします。


CC BY-SA 3.0
2016-07-28T07:57:11.983
27911
2016-08-03T07:39:31.960
2016-07-29T01:39:21.963
10083
10083
question
mysql
複数タグ検索で一致した数を元に記事をソートしたい

[code]

    SELECT COUNT(p.id), p.* FROM posts p
    JOIN post_tag pt ON p.id = pt.post_id 
    JOIN tags t ON pt.tag_id = t.id
    WHERE t.id IN (1, 2, 3)
    GROUP BY p.id;
    
[/code]

結合した結果`post`と`tag`の組み合わせ総数が求まるので、あとは`post`ごとに数をカウントすればよいかと思います。  
質問文中のクエリで問題無さそうに見えますが、もしかすると単に `posts.id = posts.id`
の部分でtypoしているせいで想定外の結果になっているのかもしれません。


CC BY-SA 3.0
2016-07-28T09:25:55.217
27914
2016-07-29T02:34:32.507
2016-07-29T02:34:32.507
2808
2808
27911
answer


既に解決済みではありますが、、

`post_tag`テーブルが`tag_id`を持っているので、`tags`テーブルを結合する必要はありません。  
（`post_tag`テーブルが`tags`テーブルに存在しない`tag_id`を持っている可能性があるなら、話は別ですが。）

以下のSQL文で十分です。

[code]

    SELECT p.*, COUNT(*) AS count 
    FROM posts AS p 
    INNER JOIN post_tag AS pt ON p.post_id = pt.post_id 
    WHERE pt.tag_id IN (1, 2, 3) 
    GROUP BY p.post_id 
    ORDER BY count DESC;
    
[/code]

<http://sqlfiddle.com/#!9/56283/1>

ただし、上のSQL文では一致するタグ数が同じレコードの並び順は不定となります。  
その並び順を制御したい場合は、`ORDER BY`句に必要な条件を追加してください。

また、取得するデータに一致したタグの数を含めたくない場合、以下のように書けます。

[code]

    SELECT p.* 
    FROM posts AS p 
    INNER JOIN post_tag AS pt ON p.post_id = pt.post_id 
    WHERE pt.tag_id IN (1, 2, 3) 
    GROUP BY p.post_id 
    ORDER BY COUNT(*) DESC;
    
[/code]


CC BY-SA 3.0
2016-08-03T07:39:31.960
28043
2016-08-03T07:39:31.960
16934
27911
answer