I'm trying to fetch the most popular tags from all videos in my database (ignoring blank tags). I also need the 'flv' for each tag. I have this working as I want if each video has one tag:
SELECT tag_1, flv, COUNT(tag_1) AS tagcount
FROM videos
WHERE NOT tag_1=''
GROUP BY tag_1
ORDER BY tagcount DESC LIMIT 0, 10
However in my database, each video is allowed three tags - tag_1, tag_2 and tag_3. Is there a way to get the most popular tags reading from multiple columns?
The record structure is:
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| flv | varchar(150) | YES | | NULL | |
| tag_1 | varchar(75) | YES | | NULL | |
| tag_2 | varchar(75) | YES | | NULL | |
| tag_3 | varchar(75) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
解决方案
You need to unpivot the data:
SELECT tag, COUNT(*)
FROM (
SELECT tag_1 AS tag
UNION ALL
SELECT tag_2 AS tag
UNION ALL
SELECT tag_3 AS tag
) AS X (tag)
GROUP BY tag
ORDER BY COUNT(*) DESC
I'm not sure how the flv is determined for a particular tag, since each id can have a single flv and up to 3 tags, it seems like any tag can have many different flv.