MySQL COUNT() 多列 [英] MySQL COUNT() multiple columns

问题描述

我正在尝试从我的数据库中的所有视频中获取最流行的标签(忽略空白标签).我还需要每个标签的flv".如果每个视频都有一个标签，我就可以按照自己的意愿工作:

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

但是在我的数据库中，每个视频都允许使用三个标签 - tag_1、tag_2 和 tag_3.有没有办法从多列中读取最流行的标签?

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?

记录结构为:

+-----------------+--------------+------+-----+---------+----------------+ 
| 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

我不确定如何为特定标签确定 flv，因为每个 id 可以有一个 flv 和最多 3 个标签，似乎任何标签都可以有许多不同的 flv.

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.

这篇关于MySQL COUNT() 多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！