sql - 涉及类似XOR条件的SQL查询问题

假设我们在SQL数据库中有一个包含以下数据的表（EnsembleMembers）。它列出了属于各种乐团的音乐家及其乐器。

EnsembleID (FK)   MusicianID (FK)   Instrument
----------------------------------------------
'1'               '1'               'Clarinet'
'1'               '4'               'Clarinet'
'1'               '100'             'Saxophone'
'2'               '200'             'Saxophone'
'2'               '300'             'Saxophone'
'2'               '320'             'Flute'
'99'              '300'             'Clarinet'

我要选择一个具有一个或多个萨克斯风或一个或多个单簧管播放器，但不能同时具有两者的集成ID。我已经尝试了以下SQL语句，但是它返回的是1,2,2,99，而不是预期的2,99。

SELECT e1.EnsembleID
  FROM ensemblemembers e1
 WHERE e1.Instrument = 'Saxophone'
    OR e1.Instrument = 'Clarinet'
  AND NOT EXISTS (SELECT *
                    FROM ensemblemembers e2
                   WHERE (    e1.Instrument = 'Saxophone'
                          AND e2.Instrument = 'Clarinet'
                          AND e1.EnsembleID = e2.EnsembleID)
                      OR (    e1.Instrument = 'Clarinet'
                          AND e2.Instrument = 'Saxophone'
                          AND e1.EnsembleID = e2.EnsembleID));

我究竟做错了什么？

PS-由于性能原因，我不想使用DISTINCT。

最佳答案

我假设您有一个名为ENSEMBLES的表：

select E.id from ensembles E where exists (
  select 1 from ensemblemembers M where E.id = M.ensembleid
  and M.instrument in ('clarinet', 'saxophone')
) and not (
  exists (
    select 1 from ensemblemembers M where E.id = M.ensembleid
    and M.instrument = 'clarinet'
  ) and exists (
    select 1 from ensemblemembers M where E.id = M.ensembleid
    and M.instrument = 'saxophone'
  )
)

您要避免使用DISTINCT，因此一种方法是使用主ENSEMBLES表。从那里，选择具有“ clarinet”或“ saxophone”的合奏行。然后，第三步是删除所有具有“ clarinet”和“ saxophone”的合奏行。