問題描述

有兩組員工:經(jīng)理和咕嚕聲.
對于每位經(jīng)理，都有一個表 manager_meetings，其中包含每位經(jīng)理參加的會議的列表.一個類似的表 grunt_meetings 包含每個 grunt 參加的會議的列表.

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

當經(jīng)理和咕嚕聲知道完全相同的信息時，所有者不喜歡它.這讓他的頭很痛.他想確定這種情況，因此他可以將經(jīng)理降級為咕嚕聲，或將咕嚕聲提升為經(jīng)理，或者讓他們一起打高爾夫球.店主喜歡打高爾夫球.

The owner doesn't like it when a manager and a grunt know exactly the same information. It makes his head hurt. He wants to identify this situation, so he can demote the manager to a grunt, or promote the grunt to a manager, or take them both golfing. The owner likes to golf.

任務是列出 manager 和 grunt 參加完全相同會議的每個組合.如果經(jīng)理參加的會議多于咕嚕聲，則無法匹配.如果咕嚕聲參加的會議比經(jīng)理多，那就沒有對手了.

The task is to list every combination of manager and grunt where both attended exactly the same meetings. If the manager attended more meeting than the grunt, no match. If the grunt attended more meetings than the manager, no match.

這里的預期結果是:

ManagerID            GruntID
2                    7
1                    5 

...因為 manager 2 和 grunt 7 都參加了 (a,b)，而 manager 1 和 grunt 5 都參加了 (a,b,c).

...because manager 2 and grunt 7 both attended (a,b), while manager 1 and grunt 5 both attended (a,b,c).

通過將子查詢中的會議子集轉換為 XML，并將每個 grunt 的 XML 列表與每個經(jīng)理的 XML 進行比較，我可以用笨拙的方式解決它.但這太可怕了，而且我還必須向所有者解釋什么是 XML.而且我不喜歡打高爾夫球.

I can solve it in a clunky way, by pivoting up the subset of meetings in a subquery into XML, and comparing each grunt's XML list to each manager's XML. But that's horrible, and also I have to explain to the owner what XML is. And I don't like golfing.

有沒有更好的方法來做"WHERE {subset1} = {subset2}"?感覺就像我錯過了某種巧妙的連接.

Is there some better way to do "WHERE {subset1} = {subset2}"? It feels like I'm missing some clever kind of join.

SQL 小提琴

推薦答案

這是一個有效的版本:

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;

字符串比較方法更短，可能更容易理解，而且可能更快.

The string comparison method is shorter, perhaps easier to understand, and probably faster.

對于獲得完全匹配的特定情況，可以簡化查詢:

For your particular case of getting exact matches, the query can be simplified:

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);

在性能和清晰度方面，這可能比字符串版本更具競爭力.

This might be more competitive with the string version, both in terms of performance and clarity.

它計算咕嚕聲和經(jīng)理之間的匹配次數(shù).然后檢查這是否是每個人的所有會議.

It counts the number of matches between a grunt and a manager. It then checks that this is all the meetings for each.

這篇關于sql選擇具有匹配子集的記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！