需要MySQL查询和PHP的帮助

I have the following 3 tables:

我有以下三张表:

(PK = Primary Key, FK = Foreign Key)

(PK =主键，FK =外键)

Files Table

File ID (PK)    File Name    ...
------------    ---------
     1            a.jpg      ...
     2            b.png      ...
     3            c.jpg      ...
     .              .
     .              .
     .              .

Tags Table

Tag ID (PK)   Tag Name       ...
-----------   ----------  
   1          Melbourne      ...
   2          April          ...
   3          2010           ...
   .           .
   .           .
   .           .

Files_Tags Table

File ID (FK)    Tag ID (FK)
------------    -----------
      1              1
      1              5
      1              7 
      2              2 
      2              4
      3              3
      .              .
      .              .
      .              .

In PHP, I want to get a list of all tags along with the number of times the tag appears (i.e. the number of files that have this tag).

在PHP中，我希望得到所有标记的列表，以及标记出现的次数(即有这个标记的文件的数量)。

Is that possible to do with one MySQL query ?

是否可以只使用一个MySQL查询?

2 个解决方案

#1

Try GROUP BY on your tag id. Use a LEFT JOIN to include tags that exist in the tags table but aren't ever used.

在标签id上尝试GROUP BY，使用左连接来包含标签表中存在的标签，但从未使用过。

SELECT
    Tag_Name, 
    COUNT(Files_Tags.Tag_ID) AS cnt
FROM Tags
LEFT JOIN Files_Tags
ON Tags.Tag_ID = Files_Tags.Tag_ID
GROUP BY Tags.Tag_ID

Result:

结果:

Melbourne  1
April      1
2010       1
...        ...

You may also want to add an ORDER BY Tag_Name or an ORDER BY COUNT(*) if you want the rows returned in sorted order.

如果您希望以排序的顺序返回行，您可能还希望通过Tag_Name或COUNT(*)添加订单。

Daniel Vassello also submitted an answer but deleted it. However his answer is quite easy to adapt to meet your new requirements. Here is his solution, modified to use a LEFT JOIN instead of an INNER JOIN:

Daniel Vassello也提交了一个答案，但是删除了。但是他的答案很容易适应你的新要求。这里是他的解决方案，修改为使用左连接而不是内部连接:

SELECT t.tag_id,
       t.tag_name,
       IFNULL(d.tag_count, 0) AS tag_count
FROM tags t
LEFT JOIN
(
    SELECT tag_id, COUNT(*) tag_count
    FROM files_tags
    GROUP BY tag_id
) d ON d.tag_id = t.tag_id;

#2

You shouldn't use too much of GROUP BY, ORDER BY and * JOIN as those query are very heavy and it's not something you should base your code on.

您不应该使用太多的GROUP BY、ORDER BY和* JOIN，因为这些查询非常繁重，您不应该将代码作为基础。

If I was you, I would do multiple simple SELECT query and group the stuff together using PHP algorithms. This way, you're DB won't be hit by very slow query.

如果我是您，我将执行多个简单的SELECT查询，并使用PHP算法对这些内容进行分组。这样，DB就不会受到非常慢的查询的影响。

So basically, in your specific question I would have more than 1 query.

基本上，在你的特定问题中，我有不止一个查询。

I would start by doing a

从a开始

SELECT * FROM "tags_table".

In php, I would created a foreach loop that would count appearance of every tag in your "files_tags" table:

在php中，我将创建一个foreach循环，计算“files_tags”表中每个标签的外观:

SELECT FILE_ID COUNT(*) FROM TAGS_TABLE WHERE TAG_ID = 'tag_uid'

It's mostly pseudo-code so I wouldn't expect those query to work but you get the general idea.

它主要是伪代码，所以我不认为这些查询有效，但你会明白的。

#1