跨越3个表的SQL join，具有多个WHERE子句匹配。

Question

我有三个表：user（用户）、user_tag（用户标签）和tag（标签）。以下是这些表的最基本元素。

用户通过中间表user_tag与标签相关联。每个用户可以有零个或多个标签。我希望找到具有一个或多个匹配标签的用户。

user（用户）

   Column    |              Type              |            Modifiers
-------------+--------------------------------+---------------------------------
 id          | integer                        | not null
 name        | character varying(150)         | not null

用户标签

   Column   |              Type              | Modifiers
------------+--------------------------------+-----------
 id         | integer                        | not null
 user_id    | integer                        |
 tag_id     | integer                        |

标签

   Column    |              Type              |            Modifiers
-------------+--------------------------------+---------------------------------
 id          | integer                        | not null
 name        | character varying(64)          | not null

因此，查找只有一个标签的用户很简单：

select u.id,u.name,g.name 
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where g.name='TAGX';

我的问题是，如何匹配两个或多个标签？

像下面这样做是行不通的。

select u.id,u.name,g.name 
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where (g.name='TAGX' and g.name='TAGY');

感觉好像需要进行第二次连接才能匹配第二个标签...？

- ritter

Answer 1

首先，将您的条件更改为：

where (g.name='TAGX' and g.name='TAGY')

to:

where (g.name='TAGX' OR g.name='TAGY')

或者：

where g.name in ('TAGX', 'TAGY')

您希望将标签TAGX和TAGY合并。

现在，您的输出应该类似于以下内容：

+----+--------+------+
| ID |  Name  | Tag  |
+----+--------+------+
|  1 | User 1 | TAGX |
|  1 | User 1 | TAGY |
|  3 | User 3 | TAGX |
|  4 | User 4 | TAGY |
+----+--------+------+

如您所述，您只想要拥有2个或更多标签的用户，结果中的用户3和4是不合适的。若要删除它们，您需要执行以下操作：

• 从选择语句中删除标签列
• 按id和名称对用户进行分组
• 计算每个用户拥有的标签数量
• 创建一个条件来过滤掉拥有少于2个标签的用户

像这样：

select u.id,u.name
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where g.name in ('TAGX', 'TAGY')
group by u.id,u.name
having count(u.id) < 2; 

那么你的输出应该是：

+----+--------+
| ID |  Name  |
+----+--------+
|  1 | User 1 |
+----+--------+

如果您想检查条件是否正确过滤，可以通过显示计数列并删除HAVING子句来进行视觉验证。像这样：

select u.id,u.name, count(u.id)
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where g.name in ('TAGX', 'TAGY')
group by u.id,u.name;

希望这能向您展示：

+----+--------+-------+
| ID |  Name  | count |
+----+--------+-------+
|  1 | User 1 |     2 |
|  3 | User 3 |     1 |
|  4 | User 4 |     1 |
+----+--------+-------+

- Roger Almeida

Answer 2

如果您想查找具有任意两个标签的用户，则Tarik的答案可以满足您的要求，但是如果您想查找具有这两个标签（以及其他标签）的用户，则此查询将实现该目的：

select u.id, u.name
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where g.name in ('TAGX', 'TAGY')
group by u.id, u.name
having count(distinct g.name) = 2; 

以上查询将返回至少具有TAGX和TAGY标签的用户，但可能具有更多标签。如果您想要拥有这两个标签且没有其他标签的用户，则一种解决方案是执行如下相关联的不存在查询：

select u.id, u.name, g.name
from user u 
join user_tag t on t.user_id = u.id 
join tag g on g.id = t.tag_id 
where not exists (
    select 1 
    from user_tag join tag on user_tag.tag_id = tag.id
    where tag.name not in ('TAGX', 'TAGY') 
    and user_tag.user_id = u.id
)

- jpw

---

我在第二个查询中犯了一个错误；它当然应该包括where、group by和having子句，就像第一个查询一样；唯一的区别是添加了not exists谓词...不过现在无法编辑，稍后会修复。 - jpw