Question

我 m身于我自己的MySQL问答技能的极限,因此,我希望某些古鲁能够帮助完成这项工作。情况如下:

我的图像可以打脚。由于您可能期望,这分为三个表格:

Image
Tag
Tag_map (maps images to tags)

我有一个问题,即根据tag子计算相关的标签。询问基本检查了使用该标签的图像所用的其他标记。例:

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

如果我把“Bear”(或其标签)扔到问点上,它将返回“加拿大”。罚款。这里要问:

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

我 m的那部分是 count。对于每一个相关方来说,我想知道这些方的图像有多少。目前,它总是回归1,即便是这样的话。 3. 。我尝试计算出不同的栏目,导致产出相同,因此,我猜测我的思维存在缺陷。

Answer 1

你的代码并不正确,因为你只选择了“与选择的标签有关”的图像,而没有图像,“与与选择的标签相关的图像相关联”(我希望,我使用正确的再入侵深度:)

你们可以做如下选择:

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;

Answer 2

www.un.org/Depts/DGACM/index_spanish.htm 一些供思考的食物

I noticed you use id in your tag & image table and tablename_id in your tag_map table. Each his own offcourse, but I found it to be much easier if an id is named the same everywhere. I would rename the id s in tag & image to tag_id & image_id respectively.
It seems your id s are character strings. I ve taken the liberty to use integers in the examples.

以下例子使用服务器。不应试图将《公报》调整到MySQL.。

www.un.org/Depts/DGACM/index_spanish.htm 测试数据

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

友情链接