需要一些MySQL子查询计数的帮助(Need some help with a MySQL subquery count)

需要一些MySQL子查询计数的帮助(Need some help with a MySQL subquery count)

我正在遇到我自己的MySQL查询技能限制，所以我希望一些SQL大师可以帮助解决这个问题。 情况如下：

我有可以标记的图像。 正如您所料，它存储在三个表中：

图片 标签 Tag_map（将图像映射到标签）

我有一个SQL查询，它根据标签ID计算相关标签。 该查询基本上使用该标签检查用于图像的图像的其他标签。 例：

图片1标签为“熊” 图片2标签为“熊”和“加拿大”

如果我在查询中抛出“熊”（或其标签ID），它将返回“加拿大”。 这很好用。 这是查询：

SELECT tag.name, tag.id, COUNT(tag_map.id) as cnt FROM tag_map,tag WHERE tag_map.tag_id = tag.id AND tag.id != '185' AND tag_map.image_id IN (SELECT tag_map.image_id FROM tag_map INNER JOIN tag ON tag_map.tag_id = tag.id WHERE tag.id = '185') GROUP BY tag_map.id LIMIT 0,100

我坚持的部分是伯爵。 对于返回的每个相关标签，我想知道该标签中有多少图像。 目前它总是返回1，即使有例如3.我已经尝试计算不同的列都导致相同的输出，所以我想我的想法有一个缺陷。

I'm running into my own limits of MySQL query skills, so I hope some SQL guru can help out on this one. The situation is as follow:

I have images that can be tagged. As you might expect this is stored in three tables:

Image Tag Tag_map (maps images to tags)

I have a SQL query that calculates the related tags based on a tag id. The query basically checks what other tags were used for images for images using that tag. Example:

Image1 tagged as "Bear" Image2 tagged as "Bear" and "Canada"

If I throw "Bear" (or its tag id) at the query, it will return "Canada". This works fine. Here's the query:

SELECT tag.name, tag.id, COUNT(tag_map.id) as cnt FROM tag_map,tag WHERE tag_map.tag_id = tag.id AND tag.id != '185' AND tag_map.image_id IN (SELECT tag_map.image_id FROM tag_map INNER JOIN tag ON tag_map.tag_id = tag.id WHERE tag.id = '185') GROUP BY tag_map.id LIMIT 0,100

The part I'm stuck with is the count. For each related tag returned, I want to know how many images are in that tag. Currently it always returns 1, even if there are for example 3. I've tried counting different columns all resulting in the same output, so I guess there is a flaw in my thinking.

最满意答案

您的代码无法正常工作，因为您只选择“与选择的标签相关联”的图像，而不是图像，“与与选择标签关联的图像相关联的标签相关联”（我希望，我使用了正确的递归深度:)）。

您可以使用子选择执行此操作：

SELECT tag.id, tag.name, COUNT(DISTINCT tag_map.image_id) as cnt FROM tag_map, tag WHERE tag_map.tag_id = tag.id AND tag.id != 185 AND tag_map.tag_id IN ( SELECT sub1.tag_id FROM tag_map AS sub1 WHERE sub1.image_id IN ( SELECT sub2.image_id FROM tag_map AS sub2 WHERE sub2.tag_id = 185 ) ) GROUP BY tag.id, tag.name;

Your code not working right because you select just images "associated with a choosen tag", but not images, "associated with tags associated with image associated with choosen tag" (I hope, I used correct recursion depth :) ).

You can do this with subselects:

SELECT tag.id, tag.name, COUNT(DISTINCT tag_map.image_id) as cnt FROM tag_map, tag WHERE tag_map.tag_id = tag.id AND tag.id != 185 AND tag_map.tag_id IN ( SELECT sub1.tag_id FROM tag_map AS sub1 WHERE sub1.image_id IN ( SELECT sub2.image_id FROM tag_map AS sub2 WHERE sub2.tag_id = 185 ) ) GROUP BY tag.id, tag.name;