MySQL A或B，但不能同时使用 [英] MySQL A or B but NOT both

问题描述

这似乎是一个简单的查询，但是我似乎无法获取它或将其与堆栈溢出时的其他帖子相关联.谁能解释...这是我到目前为止的信息，它正在返回一个或两个人去的所有酒吧的记录.

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频繁模式-饮酒者VARCHAR(50)PK，bar VARCHAR(50)PK

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

John或Rebecca经常光顾的酒吧，但两者都不是

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

---

• 获取所有bar的约翰"和/或丽贝卡"
• 将每个bar
的行折叠为一行
• 每个条形的计数为drinker
• 丢弃计数为2的行(即John和Rebecca)
• 仅保留John的bar值，而不保留Rebecca的值，反之亦然

---

• 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

这篇关于MySQL A或B，但不能同时使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！