代码之家  ›  专栏  ›  技术社区  ›  Fer

需要有关MySQL子查询计数的帮助

  •  3
  • Fer  · 技术社区  · 15 年前

    我现在遇到了我自己的MySQL查询技能的极限,所以我希望一些SQL专家能在这方面有所帮助。情况如下:

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

    • 图像
    • 标签
    • 标签映射(将图像映射到标签)

    我有一个SQL查询,它根据标记ID计算相关的标记。该查询基本上检查使用该标记的图像的其他标记。例子:

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

    如果我在查询中抛出“bear”(或其标签ID),它将返回“canada”。这个很好用。以下是问题:

    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。我试过对不同的列进行计数,结果都是相同的输出,所以我想我的想法有一个缺陷。

    2 回复  |  直到 15 年前
        1
  •  2
  •   dchekmarev    15 年前

    您的代码不能正常工作,因为您只选择了“与choosen标记相关联”的图像,而不是“与choosen标记相关联”的标记(我希望,我使用了正确的递归深度:)。

    您可以使用子选项执行此操作:

    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;
    
        2
  •  1
  •   Lieven Keersmaekers    15 年前

    一些思考的食物

    • 我注意到你用 id 在标记和图像表中 tablename_id 在你的标签地图表中。当然,每个人都有自己的身份证,但我发现如果一个身份证在任何地方都是一样的,那就容易多了。我会将tag&image中的ID分别重命名为tag&id&image&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