SQL 确定具有相同成员的团队

Question

我有一个有趣的问题。

我有一些团队，团队负责人存储在一张表中，团队成员存储在子表中。我想确定具有相同成员的团队。

TEAMS
TEAM_ID    LEADER_ID
1   1
2   1
3   2
4   2

MEMBERS
TEAM_ID MEMBER_ID
1   2
1   3
1   4
2   3
2   4
2   5
3   1
3   3
3   4
4   5
4   6
4   7

我能够编写此查询来确定编队，但现在我不知道如何继续。

SELECT
    TEAM_ID,
    (
        SELECT
            CONVERT (VARCHAR, MEMBER_ID) + ', '
        FROM
            (
                SELECT
                    TEAM_ID,
                    LEADER_ID AS MEMBER_ID
                FROM
                    TEAMS
                UNION ALL
                    SELECT
                        TEAM_ID,
                        MEMBER_ID
                    FROM
                        MEMBERS
            ) FORMATIONS
        WHERE
            TEAM_ID = MT.TEAM_ID
        ORDER BY
            MEMBER_ID FOR XML PATH ('')
    ) AS MEMBERS
FROM
    TEAMS MT

很明显team id 1和3是一样的，如何得到重复的teams中最低的ID。

即查询应返回每个重复组中最小的 TEAM_ID 列表(并且仅当它们重复时)

在这种情况下，应该返回 id 1。

http://sqlfiddle.com/#!18/c845a/5

Answer 1

有比将成员填充到字符串中并比较它们更糟糕的方法来解决这个问题。所以，我会按照你开始的路线走。

您需要做的就是合并两个表中的成员，然后将其用于逻辑:

with m as (
       select team_id, member_id
       from members
       union  -- on purpose to remove duplicates
       select team_id, leader_id
       from teams
      )
select *
from (select team_id, members, count(*) over (partition by members) as num_teams
      from (select t.team_id,
                   stuff( (select concat(',', m.member_id)
                           from m
                           where m.team_id = t.team_id
                           order by m.member_id
                           for xml path ('')
                          ), 1, 1, ''
                        ) as members
            from teams t
           ) t
     ) t
where num_teams > 1
order by members;

Here是你的 SQL fiddle 。

请注意，字符串比较适用于这种情况，它是成员的精确 匹配。对于超集关系，效果不是很好。