PSQL查找不同组中的常见值

Lets say I have a bunch of team_id's.

让我们说我有一堆team_id。

team_id|username
-------|--------
      1|      u1
      1|      u2
      2|      u3
      2|      u1
      2|      u2
      3|      u1
      3|      u2
      3|      u4

So for every team_id I want to find if any two users are always have the same team_id. So in the example above, the query should return u1 and u2 since they always in the same team. Kind of hard to explain...any ideas?

因此，对于每个team_id，我想找到任何两个用户是否总是拥有相同的team_id。因此，在上面的示例中，查询应返回u1和u2，因为它们始终位于同一个团队中。有点难以解释......有什么想法吗？

1 个解决方案

#1

build data:

构建数据：

t=# create table so17(team_id int,username text);
CREATE TABLE
t=# copy so17 from stdin delimiter '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>       1|      u1
      1|      u2
      2|      u3
      2|      u1
      2|      u2
      3|      u1
      3|      u2
      3|      u4>> >> >> >> >> >> >>
>> \.
COPY 8

select:

选择：

t=# with e as (with i as (select *,array_agg(team_id) over (partition by username) from so17)
select distinct username,count(1) over (partition by array_agg) from i)
select username from e where count > 1;
 username
----------
       u2
       u1
(2 rows)