代码之家 › 专栏 › 技术社区 › Jake Petroules

涉及类异或条件的SQL查询问题

mysql sql

Jake Petroules · 技术社区 · 14 年前

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

我做错什么了?

4 回复 | 直到 14 年前

Endy Tjahjono 14 年前

我想你有一张叫做“合奏”的桌子:

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,一种方法是使用主集合表。从那里,选择有“单簧管”或“萨克斯管”的合奏行。然后第三步是删除所有有“单簧管”和“萨克斯管”的合奏行。

Bill Karwin 14 年前

SELECT EnsembleID
FROM EnsembleMembers
WHERE Instrument IN ('Saxophone', 'Clarinet')
GROUP BY EnsembleID
HAVING COUNT(DISTINCT Instrument) = 1

您还可以为此使用完全外部联接,但MySQL和其他一些小型数据库不支持这种联接。

SELECT COALESCE(e1.EnsembleID, e2.EnsembleID) AS EnsembleID
FROM EnsembleMembers e1 FULL OUTER JOIN EnsembleMembers e2
  ON e1.EnsembleID = e2.EnsembleID 
  AND e1.Instrument = 'Saxophone' 
  AND e2.Instrument = 'Clarinet'
WHERE e1.EnsembleID IS NULL OR e2.EnsembleID IS NULL

SELECT e1.EnsembleID, e1.Instrument
FROM EnsembleMembers e1 LEFT OUTER JOIN EnsembleMembers e2
  ON e1.EnsembleID = e2.EnsembleID 
  AND e2.Instrument = 'Clarinet'
WHERE e1.Instrument = 'Saxophone' AND e2.EnsembleID IS NULL
UNION
SELECT e1.EnsembleID, e1.Instrument, e2.EnsembleID, e2.Instrument
FROM EnsembleMembers e1 LEFT OUTER JOIN EnsembleMembers e2
  ON e1.EnsembleID = e2.EnsembleID 
  AND e2.Instrument = 'Saxophone'
WHERE e1.Instrument = 'Clarinet' AND e2.EnsembleID IS NULL;

以后,请用你使用的RDBMS品牌来标记你的问题。

Alexander Malakhov Rahul R Dhobi 14 年前

   SELECT EnsembleID FROM EnsembleMembers
MINUS
( 
      SELECT EnsembleID
        FROM EnsembleMembers
       WHERE Instrument = 'Saxophone'
   INTERSECT
      SELECT EnsembleID
        FROM EnsembleMembers
       WHERE Instrument = 'Clarinet' );

kristian 14 年前

下面是使用XOR进行查询的方法:

select a.EnsembleID from 
(
    select max(EnsembleID) as EnsembleID,
    max(case when Instrument = 'Saxophone' then 1 else 0 end) as Saxophone,
    max(case when Instrument = 'Clarinet' then 1 else 0 end) as Clarinet
    from 
        EnsembleMembers
    group by EnsembleID
) a 
where 
    a.Saxophone ^ a.Clarinet = 1