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.