mysql a-b,MySQL A或B，但不能同时使用

This seems like an easy query but I cannot seem to get it or relate it to other posts on stack overflow. Would anyone be able to explain... This is what I have so far, it is returning records for all bars where one or both people go.

TBL frequents Schema - drinker VARCHAR(50) PK, bar VARCHAR(50) PK

Bars which are frequented by John or Rebecca but not by both of them

SELECT DISTINCT bar

FROM frequents

WHERE drinker = 'John' XOR drinker = 'Rebecca'

AND bar NOT IN (

SELECT f1.bar

FROM frequents f1, frequents f2

WHERE (

f1.drinker = 'John'

AND f2.drinker = 'Rebecca'

AND f1.bar = f2.bar

)

);

解决方案

Something like this should satisfy the specification:

SELECT f.bar

FROM frequents f

WHERE f.drinker IN ('John','Rebecca')

GROUP

BY f.bar

HAVING COUNT(DISTINCT f.drinker) < 2

get all of bar for 'John' and/or 'Rebecca'

collapse the rows to a single row for each bar

get a count of drinker for each bar

discard rows that have a count of 2 (i.e. both John and Rebecca)

leaving only values of bar for John and not Rebecca or vice versa