Postgres独家标签搜索(Postgres exclusive tag search)

我正在尝试返回与所有查询的“标记”相关联的用户关联的所有行。我的表结构和所需的输出如下：

由于user_id 2与'apple'和'pear'相关联，因此返回她的'apple'和'pear'行中的每一行，并加入system.users以便还返回她的电子邮件。

我对如何正确设置这个postgresql查询感到困惑。我已经用左反连接进行了多次尝试，但似乎无法获得所需的结果。

I'm trying to return all rows which are associated with a user who is associated with ALL of the queried 'tags'. My table structure and desired output is below:

Since user_id 2 is associated with both 'apple' and 'pear' each of her 'apple' and 'pear' rows are returned, joined to system.users in order to also return her email.

I'm confused on how to properly set up this postgresql query. I have made several attempts with left anti-joins, but cannot seem to get the desired result.

最满意答案

派生表中的查询为您提供具有所有指定标记的用户的用户ID，外部查询将为您提供详细信息。

select * from "system.users" s join "admin.tags" a on s.id = a.user_id join ( select user_id from "admin.tags" where tag in ('apple', 'pear') group by user_id having count(distinct tag) = 2 ) t on s.id = t.user_id;

请注意，此查询将包括同时具有您搜索的两个标记但只有至少具有指定的标记的用户。

使用您的示例数据，输出将是：

| id | email | user_id | tag | detail | date | user_id | |----|---------------|---------|-------|------------|------------------------|---------| | 2 | jane@test.com | 2 | grape | blahhhhh | July, 28 2015 00:00:00 | 2 | | 2 | jane@test.com | 2 | apple | blah, blah | July, 25 2015 00:00:00 | 2 | | 2 | jane@test.com | 2 | pear | blahblah | July, 23 2015 00:00:00 | 2 | | 2 | jane@test.com | 2 | apple | blah... | July, 14 2015 00:00:00 | 2 |

如果你想用grape排除行，只需where tag in ('apple', 'pear')添加where tag in ('apple', 'pear')到外部查询。

如果您只想要只有搜索过的标签而没有其他用户的用户（例如精确划分），您可以将派生表中的查询更改为：

select user_id from "admin.tags" group by user_id having sum(case when tag = 'apple' then 1 else 0 end) >= 1 and sum(case when tag = 'pear' then 1 else 0 end) >= 1 and sum(case when tag not in ('apple','pear') then 1 else 0 end) = 0

鉴于您的样本数据，这不会返回任何内容，因为用户2也有葡萄

示例SQL小提琴

The query in the derived table gets you the user ids for users that have all specified tags and the outer query gets you the details.

select * from "system.users" s join "admin.tags" a on s.id = a.user_id join ( select user_id from "admin.tags" where tag in ('apple', 'pear') group by user_id having count(distinct tag) = 2 ) t on s.id = t.user_id;

Note that this query would include users who have both tags that you search for but may have other too as long as they at least have the two specified.

With your sample data the output would be:

| id | email | user_id | tag | detail | date | user_id | |----|---------------|---------|-------|------------|------------------------|---------| | 2 | jane@test.com | 2 | grape | blahhhhh | July, 28 2015 00:00:00 | 2 | | 2 | jane@test.com | 2 | apple | blah, blah | July, 25 2015 00:00:00 | 2 | | 2 | jane@test.com | 2 | pear | blahblah | July, 23 2015 00:00:00 | 2 | | 2 | jane@test.com | 2 | apple | blah... | July, 14 2015 00:00:00 | 2 |

If you want to exclude the row with grape just add a where tag in ('apple', 'pear') to the outer query too.

If you want only users that have only the searched for tags and none other (eg. exact division) you can change the query in the derived table to:

select user_id from "admin.tags" group by user_id having sum(case when tag = 'apple' then 1 else 0 end) >= 1 and sum(case when tag = 'pear' then 1 else 0 end) >= 1 and sum(case when tag not in ('apple','pear') then 1 else 0 end) = 0

This would not return anything given your sample data as user 2 also has grape

Sample SQL Fiddle

更多推荐