主页 / coding / 问题 / 79325001
Accepted
RobertW
Asked: 2025-01-03 05:25:15 +0800 CST

当至少有一个匹配时获取所有相关行

  • 772

我有一张photos表、一张tags表和一张桥表，分别称为photos_tags。假设有一张照片被标记为dog、cat和bird。这意味着 中有 1 行photos， 中有 1 行tags， 中有 3 行photos_tags。

我正在尝试编写一条 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'。

有没有什么办法可以完成我想做的事情？

sql

4 个回答

  1. Best Answer

    首先识别所有带有“狗”标签的照片，你已经这样做了，但我将为 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)
    • 2

  2. @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))
    • 0

  3. 假设一个标准的多对多实现，我期望这是最快和最简单的：

    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;

    只需做一次“绕罗西一圈”即可。

    • 0

  4. 可以按如下方式进行：

    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'
);
    • -1

相关问题