SQL 查询连接中的所有记录都匹配某个条件吗？

发布于 2024-10-13 04:16:52 字数 441 浏览 7 评论 0原文

我有一个看似简单的问题，但无法通过 SQL 找出正确的解决方案。我专门使用postgresql。

采取以下措施：

SELECT *
FROM users INNER JOIN tags
  ON (tags.user_id = users.id)
WHERE tags.name IN ('word1', 'word2')

这不能满足我的需要。我想找到标签仅包含在列表中的用户。如果用户的标签不在列表中，则不应包含该用户。

“user1”标签：word1、word2、word3
“user2”标签：word1
'user3' 标签：word1、word2

给定：word1 和 word2。我想准备一个返回“user2”和“user3”的查询。 “user1”被排除，因为它具有不在列表中的标签。

希望我说清楚了。感谢您的帮助！

原文

I have what seems to be a simple problem, but can not figure out the proper solution via SQL. I'm using postgresql specifically.

Take the following:

SELECT *
FROM users INNER JOIN tags
  ON (tags.user_id = users.id)
WHERE tags.name IN ('word1', 'word2')

This does not do what I need. I want to find users whose tags are ONLY included in the list. If the user has a tag that is not in the list, the user should not be included.

'user1' tags: word1, word2, word3
'user2' tags: word1
'user3' tags: word1, word2

Given: word1 and word2. I want to prepare a query that returns 'user2' and 'user3'. 'user1' is excluded because it has a tag that is not in the list.

Hopefully I made this clear. Thanks for your help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

贵在坚持 2024-10-20 04:16:52

依赖 COUNT(*) = 2 将要求标签表中的 user_id 和 name 不能重复。如果是这样的话，我就会走那条路。否则，这应该有效：

SELECT u.* 
FROM users AS u
WHERE u.id NOT IN (
    SELECT DISTINCT user_id FROM tags WHERE name NOT IN ('word1', 'word2')
) AND EXISTS (SELECT user_id FROM tags WHERE user_id = u.id)

Relying on COUNT(*) = 2 will require that there can be no duplicates of user_id and name in the tags table. If that's the case, I'd go that route. Otherwise, this should work:

SELECT u.* 
FROM users AS u
WHERE u.id NOT IN (
    SELECT DISTINCT user_id FROM tags WHERE name NOT IN ('word1', 'word2')
) AND EXISTS (SELECT user_id FROM tags WHERE user_id = u.id)

回复收藏 0 原文

花期渐远 2024-10-20 04:16:52

要获取没有列表中未包含的标签的所有用户，请使用下面的查询。返回的用户可能没有标签或只有一个与单词匹配的标签，但我知道这是所需的功能。

SELECT
  u.*
FROM
  users u
  LEFT JOIN tags t 
    ON t.user_id = u.userid AND
       t.name NOT IN ('word1', 'word2')
WHERE
  t.user_id IS NULL

To get all users that don't have a tag that is not in the list, use the query below. Could be that users are returned that have no tag or only one tag matching the words, but I understand that is the desired functionality.

SELECT
  u.*
FROM
  users u
  LEFT JOIN tags t 
    ON t.user_id = u.userid AND
       t.name NOT IN ('word1', 'word2')
WHERE
  t.user_id IS NULL

回复收藏 0 原文

柠檬 2024-10-20 04:16:52

SELECT  user_id
FROM    users
WHERE   id IN
        (
        SELECT  user_id
        FROM    tags
        )
        AND id NOT IN
        (
        SELECT  user_id
        FROM    tags
        WHERE   name NOT IN ('word1', 'word2')
        )

或者

SELECT  u.*
FROM    (
        SELECT  DISTINCT user_id
        FROM    tags
        WHERE   name IN ('word1', 'word2')
        ) t
JOIN    users u
ON      u.id = t.user_id
        AND t.user_id NOT IN
        (
        SELECT  user_id
        FROM    tags
        WHERE   name NOT IN ('word1', 'word2')
        )

SELECT  user_id
FROM    users
WHERE   id IN
        (
        SELECT  user_id
        FROM    tags
        )
        AND id NOT IN
        (
        SELECT  user_id
        FROM    tags
        WHERE   name NOT IN ('word1', 'word2')
        )

SELECT  u.*
FROM    (
        SELECT  DISTINCT user_id
        FROM    tags
        WHERE   name IN ('word1', 'word2')
        ) t
JOIN    users u
ON      u.id = t.user_id
        AND t.user_id NOT IN
        (
        SELECT  user_id
        FROM    tags
        WHERE   name NOT IN ('word1', 'word2')
        )

回复收藏 0 原文

長街聽風 2024-10-20 04:16:52

SELECT distinct users.id
FROM users 
INNER JOIN tags ON (tags.user_id = users.id) 
group by users.id 
having count(*) = 2 
and min(tags.name) = 'word1'
and max(tags.name) = 'word2'

SELECT distinct users.id
FROM users 
INNER JOIN tags ON (tags.user_id = users.id) 
group by users.id 
having count(*) = 2 
and min(tags.name) = 'word1'
and max(tags.name) = 'word2'

回复收藏 0 原文

聽兲甴掵 2024-10-20 04:16:52

SELECT u.*
FROM users u
INNER JOIN (
  SELECT user_id FROM tags WHERE name IN ('word1', 'word2')
  EXCEPT
  SELECT user_id FROM tags WHERE name NOT IN ('word1', 'word2')
) s ON u.id = s.user_id

SELECT u.*
FROM users u
INNER JOIN (
  SELECT user_id FROM tags WHERE name IN ('word1', 'word2')
  EXCEPT
  SELECT user_id FROM tags WHERE name NOT IN ('word1', 'word2')
) s ON u.id = s.user_id

回复收藏 0 原文

~没有更多了~