sql - Postgres 专属标签搜索

Question

我正在尝试返回与某个用户相关联的所有行，而该用户与所有查询的“标签”相关联。我的表结构和所需的输出如下:

admin.tags:
user_id |   tag   |   detail   |    date
   2    |  apple  | blah...    | 2015/07/14
   3    |  apple  | blah.      | 2015/07/17
   1    |  grape  | blah..     | 2015/07/23
   2    |  pear   | blahblah   | 2015/07/23
   2    |  apple  | blah, blah | 2015/07/25
   2    |  grape  | blahhhhh   | 2015/07/28 

system.users:
id  |    email
 1  | joe@test.com
 2  | jane@test.com
 3  | bob@test.com

queried tags:
'apple', 'pear'

desired output:
user_id |   tag   |   detail   |    date    |  email
   2    |  apple  | blah...    | 2015/07/14 | jane@test.com
   2    |  pear   | blahblah   | 2015/07/23 | jane@test.com
   2    |  apple  | blah, blah | 2015/07/25 | jane@test.com

由于 user_id 2 与 'apple' 和 'pear' 相关联，她的每个 'apple' 和 'pear' 行都被返回，加入到 system.users 以便也返回她的电子邮件.

我对如何正确设置此 postgresql 查询感到困惑。我已经多次尝试使用左反连接，但似乎无法获得预期的结果。

Answer 1

派生表中的查询为您提供具有所有指定标签的用户的用户 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') 即可。

如果您只想要只搜索过标签的用户而不需要其他(例如精确划分)，您可以将派生表中的查询更改为:

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 也有葡萄

Sample SQL Fiddle