MySQL SELECT每个ID的存在计数

I have a table of images and I have another table of image tags.

我有一张图像表，我有另一张图像标签表。

I'm trying to write a SQL query that will get the number of valid tags per Image ID.

我正在尝试编写一个SQL查询，它将获得每个Image ID的有效标记数。

My initial attempt was this:

我最初的尝试是这样的：

SELECT b.ID AS ID,  COUNT(a.ID) AS NUM
FROM tbl_tags a, tbl_images b 
WHERE (
    b.ID = a.IMG_ID AND 
      (a.TAG_NAME LIKE '%scenic%' OR 
       a.TAG_NAME LIKE '%fruit%' OR 
       a.TAG_NAME LIKE '%bench%' OR 
       a.TAG_NAME LIKE '%bird%'
      )
);

The result was this:

结果如下：

ID    NUM
7      13

However, what I want is to show the count value of tags for EACH Image ID found. What I'm looking for is something more like this:

但是，我想要的是显示找到的每个图像ID的标签的计数值。我正在寻找的是更像这样的东西：

ID    NUM
3      2     -- Image ID #3 has 2 tags that match the query
68     1     -- Image ID #68 has 1 tag that matches the query
87     3     -- ...
92     2     -- ...
187    1     -- ...
875    2     -- ...

4 个解决方案

#1

Here is an option using REGEXP which cleans up the WHERE clause a bit:

这是一个使用REGEXP的选项，它清理了WHERE子句：

SELECT
    a.ID,
    COUNT(b.ID) AS NUM
FROM tbl_images a
INNER JOIN tbl_tags b
    ON a.ID = b.IMG_ID
WHERE
    b.TAG_NAME REGEXP 'scenic|fruit|bench|bird'
GROUP BY
    a.ID;

#2

SELECT a.IMG_ID,  COUNT(a.ID) AS NUM
FROM tbl_tags a
WHERE a.TAG_NAME LIKE '%scenic%' OR 
       a.TAG_NAME LIKE '%fruit%' OR 
       a.TAG_NAME LIKE '%bench%' OR 
       a.TAG_NAME LIKE '%bird%'      
Group by a.IMG_ID

#3

Prefer the newer (since the 90's!) join syntax

更喜欢较新的（自90年代开始！）加入语法

SELECT b.ID AS ID,  COUNT(a.ID) AS NUM
FROM tbl_tags a
 INNER JOIN tbl_images b on b.ID = a.IMG_ID
…

as it is much clearer.

因为它更清楚。

To group when using aggregates use GROUP BY

要在使用聚合时进行分组，请使用GROUP BY

SELECT a.IMG_ID AS ID, COUNT(a.ID) AS NUM
FROM tbl_tags a
WHERE a.TAG_NAME LIKE '%scenic%' 
  OR a.TAG_NAME LIKE '%fruit%' 
  OR a.TAG_NAME LIKE '%bench%'
  OR a.TAG_NAME LIKE '%bird%'
GROUP BY a.IMG_ID

Note you don't really need the join here as you're not using anything from the tbl_Images.

请注意，您并不需要此处的连接，因为您没有使用tbl_Images中的任何内容。

#4

You don't need a join at all for this, because the image id is in tbl_tags:

您根本不需要连接，因为图像ID位于tbl_tags中：

SELECT t.IMG_ID AS ID, COUNT(*) AS NUM
FROM tbl_tags t
WHERE t.TAG_NAME LIKE '%scenic%' OR 
      t.TAG_NAME LIKE '%fruit%' OR 
      t.TAG_NAME LIKE '%bench%' OR 
      t.TAG_NAME LIKE '%bird%'
GROUP BY t.img_id;

I strongly advise you to use abbreviations for table names (such as t for tbl_tags) rather than arbitrary letters. It makes the query much easier to follow.

我强烈建议您使用表名的缩写（例如t表示tbl_tags）而不是任意字母。它使查询更容易遵循。

#1