需要帮助解决一个关于MySQL子查询计数的问题。

Question

需要帮助解决一个关于MySQL子查询计数的问题。

sqlmysqlsubquery

3

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

我有可以被标记的图片。正如你所期望的那样，这些图片存储在三个表中：

- 图片 - 标签 - 标签映射（将图片与标签对应）

我有一个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张图片。我尝试了计算不同的列，但结果都相同，所以我想我的思路存在缺陷。

- Fer

注意，'185'只是为了测试而硬编码的标签ID。 - Fer

2个回答

1

一些值得思考的事情

我发现你在标签和图片表中使用了id，而在标签映射表中使用了tablename_id。当然每个人都有自己的做法，但我发现如果一个id在所有地方都命名相同，会更容易些。我建议将标签和图片表中的id重命名为tag_id和image_id。
看起来你的id是字符串。我已经在示例中使用了整数。

以下示例使用SQL Server。调整SQL语句以适应MySQL不应该太难。

测试数据

DECLARE @tag TABLE (id INTEGER, tag VARCHAR(32))
DECLARE @image TABLE (id INTEGER, image VARCHAR(32))
DECLARE @tag_map TABLE (image_id INTEGER, tag_id INTEGER)

INSERT INTO @tag
SELECT 185, 'Bear' 
UNION ALL SELECT 186, 'Canada'

INSERT INTO @image
SELECT 1, 'image1'
UNION ALL SELECT 2, 'image2'

INSERT INTO @tag_map
SELECT 1, 185
UNION ALL SELECT 2, 185
UNION ALL SELECT 2, 186

SQL语句

SELECT  t.tag
        , t.id
        , cnt = (SELECT COUNT(*) FROM @tag_map WHERE tag_id = t.id)
FROM    @tag_map m
        INNER JOIN @tag t ON t.id = m.tag_id
        INNER JOIN (
          SELECT  m.image_id
          FROM    @tag_map m
          WHERE   m.tag_id = 185
        ) i ON i.image_id = m.image_id
WHERE   t.id <> 185

- Lieven Keersmaekers

感谢您详细的回复。我将您的查询调整为MySQL语法，并更改了列名称以适应我的表：SELECT t.name，t.id，COUNT（*） FROM tag_map as m INNER JOIN tag as t ON t.id = m.tag_id INNER JOIN ( SELECT m.image_id FROM tag_map as m WHERE m.tag_id = 185 ) as i ON i.image_id = m.image_id WHERE t.id <> 185 GROUP BY t.name，t.id不幸的是，它仍然返回1，而应该返回3。我认为它在计算相关标签数量，但我想计算具有该相关标签的图像数量。 - Fer

@Ferdy，我已经更改了查询。你能验证一下结果吗？ - Lieven Keersmaekers

现在它可以工作了，非常感谢。鉴于其他答案，我现在有两个解决方案 :) 我要抛硬币来决定谁赢。谢谢你们俩。 - Fer

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- dchekmarev · Accepted Answer

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

您可以使用子查询来解决此问题：

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;