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

当至少有一个匹配时,尝试获取所有相关记录

many-to-many join postgresql sql

RobertW · 技术社区 · 10 月前

我有一个 photos 桌子,a tags 桌子,以及一个名为 photos_tags 想象一下,有一张照片被标记为 dog , cat ,以及 bird 。这意味着将有1行 照片 ,1行 标签 ,中有3行 照片标签 .

我试图编写一个SQL语句,如果其中一个标签匹配,则获取所有3个标签。我已经走了这么远:

select distinct p.photo_id, t.tag from photos p
join photos_tags pt using (photo_id)
join tags t on pt.tag_id = t.tag_id and t.tag = 'dog'

但这只返回以下行 tag = 'dog' .

有没有办法完成我想做的事情?

2 回复 | 直到 10 月前

JNevill 10 月前

首先,确定所有带有“狗”标签的照片,你已经这样做了,但我要为CTE重写

SELECT photo_id
FROM photos_tags
WHERE tag_id IN (SELECT tag_id FROM tags WHERE tag = 'dog')

现在,使用此功能筛选您的查询以显示所有 tags 对于任何 photo_id 这一点浮出水面:

WITH dog_photos AS 
(
   SELECT photo_id
   FROM photos_tags
   WHERE tag_id IN (SELECT tag_id FROM tags WHERE tag = 'dog')
)
SELECT p.photo_id, t.tag 
FROM photos p
  INNER JOIN photos_tags pt USING (photo_id)
  INNER JOIN tags t ON pt.tag_id = t.tag_id 
WHERE p.photo_id IN (SELECT photo_id FROM dog_photos)

Dale K 10 月前

@JNevill 提供了一个答案,我将把它标记为正确的解决方案。为了完整起见,我还将分享一种我发现似乎也有效的替代方法:

select distinct p.photo_id, array_agg(t.tag) as tags from photos p
join photos_tags pt using (photo_id)
join tags t on pt.tag_id = t.tag_id
group by p.photo_id
having 'dog' = any(array_agg(t.tag))

Erwin Brandstetter 10 月前

假设a standard many-to-many implementation ,我希望这是最快、最简单的:

SELECT photo_id, array_agg(t1.tag) AS tags
FROM   tags        t
JOIN   photos_tags pt  USING (tag_id)
JOIN   photos_tags pt1 USING (photo_id)
JOIN   tags        t1  ON t1.tag_id = pt1.tag_id
WHERE  t.tag = 'dog'
GROUP  BY 1;

只要做一次“绕着罗西转一圈”。

David 10 月前

可以按如下方式进行:

SELECT DISTINCT p.photo_id, t.tag
FROM photos p
JOIN photos_tags pt ON p.photo_id = pt.photo_id
JOIN tags t ON pt.tag_id = t.tag_id
WHERE p.photo_id IN (
    SELECT p1.photo_id
    FROM photos p1
    JOIN photos_tags pt1 ON p1.photo_id = pt1.photo_id
    JOIN tags t1 ON pt1.tag_id = t1.tag_id
    WHERE t1.tag = 'dog'
);