MySQL获取最流行的标记ids及其计数组合。

Question

我有三张桌子

1.tbl_tag

id int NOT NULL AI PK
name VARCHAR(50) NOT NULL

内容

id name
1 lifestyle
2 store
3 car
4 luxury
5 classy
6 boy
7 girl
8 man
9 woman
10 strong
etc ...

2.tbl_media

id int NOT NULL AI PK
name VARCHAR(50) NOT NULL

内容

id name
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
10 ten
etc...

3. tbl_media_tag

id int NOT NULL AI PK
media_id int NOT NULL -> relationship with tbl_media
tag_id int NOT NULL -> relationship with tbl_tag

内容

id media_id tag_id
1 1 4
2 1 3
3 4 3
4 5 2
5 6 8
6 4 4
7 8 7
8 7 4
9 7 3
10 6 2
11 8 4
12 8 3
etc...

我想得到最受欢迎的标签(Tag_id)组合和限制，例如最流行的十大标签组合

我想得到这样的结果

4, 7, 3 (75)
4, 8 (42)
1, 7, 9, 10 (28)
7, 8, 9 (10)
7, 2 (2)

Answer 1

SELECT *
FROM 
    (SELECT tag_sum,
           COUNT(*) as tag_sum_count
    FROM 
        (SELECT GROUP_CONCAT(DISTINCT tag_id 
                             ORDER BY tag_id DESC 
                             SEPARATOR ',') as tag_sum,
              media_id
        FROM tbl_media_tag mt
        GROUP BY media_id) sub
    GROUP BY tag_sum) sub2
ORDER BY tag_sum_count DESC
LIMIT 10