sql选择具有匹配子集的记录

There are two sets of employees: managers and grunts.
For each manager, there's a table manager_meetings that holds a list of which meetings each manager attended. A similar table grunt_meetings holds a list of which meetings each grunt attended.

所以:

manager_meetings                          grunt_meetings
managerID      meetingID                  gruntID        meetingID
1              a                          4              a
1              b                          4              b
1              c                          4              c
2              a                          4              d
2              b                          5              a 
3              c                          5              b
3              d                          5              c
3              e                          6              a 
                                          6              c 
                                          7              b 
                                          7              a

当经理和咕噜声知道完全相同的信息时，所有者不喜欢它.这让他的头很痛.他想确定这种情况，因此他可以将经理降级为咕噜声，或将咕噜声提升为经理，或者让他们一起打高尔夫球.店主喜欢打高尔夫球.

ManagerID            GruntID
2                    7
1                    5 

select m.mId, g.gId, count(*) --select m.mid, g.gid, mm.meetingid, gm.meetingid as gmm
from manager m cross join
     grunt g left outer join
     (select mm.*, count(*) over (partition by mm.mid) as cnt
      from manager_meeting mm
     ) mm
     on mm.mid = m.mId full outer join
     (select gm.*, count(*) over (partition by gm.gid) as cnt
      from grunt_meeting gm
     ) gm
     on gm.gid = g.gid and gm.meetingid = mm.meetingid 
group by m.mId, g.gId, mm.cnt, gm.cnt
having count(*) = mm.cnt and mm.cnt = gm.cnt;

select mm.mId, gm.gId
from (select mm.*, count(*) over (partition by mm.mid) as cnt
      from manager_meeting mm
     ) mm join
     (select gm.*, count(*) over (partition by gm.gid) as cnt
      from grunt_meeting gm
     ) gm
     on gm.meetingid = mm.meetingid and
        mm.cnt = gm.cnt
group by mm.mId, gm.gId
having count(*) = max(mm.cnt);