选择连接表中多个值与给定集合匹配的位置

Question

我正在使用 Postgresql。我有一个项目表、一个标签表和一个将多个标签链接到一个项目的表。我需要选择与 2 个（或更多）给定标签匹配的项目。

我可以选择与 WHERE tag.name IN ('tag1', 'tag2') 匹配 1 个或多个的项目 在 MySQL 中我想我可以说 WHERE x IN ALL ( y, z)，但是 pgsql 似乎不起作用。

到目前为止，我最好的方法是使用子查询将两个表连接两次。这显然不会扩展到匹配许多标签，而且我确信这不是最有效的方法。

SELECT * 
FROM item 
JOIN (SELECT item.id FROM item 
      JOIN tagged on tagged.item=item.id 
      JOIN tag ON tag.id=taged.tag 
      WHERE tag.name='tagOne') p ON p.id=item.id
JOIN tagged ON tagged.item=item.id
JOIN tag ON tag.id=tagged.tag
WHERE tag.name='tagTwo'`

编辑：我仍在测试，但在子查询中使用它比我上面的尝试效果更好

SELECT item.id, count(tag2) AS relevance
FROM item
JOIN tagged tagged1 ON tagged1.item=item.id
JOIN tag tag1 ON (tag1.id=tagged1.tag AND tag1.name='tag1')
JOIN tagged tagged2 ON tagged2.item=item.id
JOIN tag tag2 ON (tag2.id=tagged2.tag)
WHERE tag2.name IN ('tag2', 'tag3')
GROUP BY item.id

根据要求，这里有一些表定义以供澄清：

CREATE TABLE item (id serial, [...]);
CREATE TABLE tag (id serial, name string UNIQUE);
CREATE TABLE taged (tag int references tag(id), item int references item(id));

Answer 1

这里有 3 种（多种）可能的方法：

select * 
from item 
where id in ( select tagged.item
              from tagged join tag on(tag.id=taged.tag)
              where tag.name in('tagOne', 'tagTwo')
              group by tagged.item
              having count(*)=2 );

select * 
from item join ( select tagged.item, count(*) as numtags 
                 from tagged join tag on(tag.id=taged.tag)
                 where tag.name in('tagOne', 'tagTwo')
                 group by tagged.item ) using (id)
where numtags=2;

select *
from item
where id in ( select tagged.item
              from tagged join tag on(tag.id=taged.tag)
              where tag.name='tagOne'
              intersect
              select tagged.item
              from tagged join tag on(tag.id=taged.tag)
              where tag.name='tagTwo' );

如果您只想要 2 个或更多匹配项，但您不介意匹配哪些标签：

select * 
from item 
where id in ( select item
              from tagged
              group by item
              having count(*)>=2 );

Answer 2

我不确定我是否理解了，但也许你可以简单地写：

WHERE tag.name IN (y) AND tag.name IN (z)

Answer 3

我喜欢使用这种形式：

SELECT *
FROM item
WHERE EXISTS (SELECT 1 FROM tagged JOIN tag ON tag.id = tagged.tag
              WHERE tag.name = 'tag1')
      AND
      EXISTS (SELECT 1 FROM tagged JOIN tag ON tag.id = tagged.tag
              WHERE tag.name = 'tag2')

您断言“这显然不会扩展到匹配许多标签，而且我确信这不是最有效的方法”——但这听起来像是您在猜测？