如何在MySQL中满足特定条件时排除行

Question

我有两个数据表。其中一个表包含参与者(participants)的信息。和一个表，其中包含事件的所有注册(Registrars)。

+----+-------+
| id | name  |
+----+-------+
| 1  | Peter |
+----+-------+
| 2  | John  |
+----+-------+
+-----------+----------+
| person_id | event_id |
+-----------+----------+
| 1         | 1        |
+-----------+----------+
| 2         | 2        |
+-----------+----------+
| 2         | 1        |
+-----------+----------+

我运行MySQL查询来获取事件注册者的信息。查询如下所示:

SELECT name
FROM Participants
INNER JOIN Registrars 
ON Participants.id=Registrars.person_id
WHERE 
event_id = 1

现在我想构建一个查询，如果参与者也注册了事件2，它将不会在查询结果中返回reg。我如何在一个查询中实现这一点?

Answer 1

可以按参与者分组，并在HAVING子句中使用条件聚合来设置条件:

SELECT p.id, p.name
FROM Participants p INNER JOIN Registrars  r
ON p.id = r.person_id
WHERE r.event_id IN (1, 2)
GROUP BY p.id, p.name
HAVING MAX(r.event_id = 1) = 1 
AND MAX(r.event_id = 2) = 0 

您可以通过以类似的方式添加更多条件来扩展代码。

Answer 2

SELECT name
FROM Participants as p
INNER JOIN Registrars as reg
ON P.id=Reg.person_id
WHERE 
reg.event_id = 1
and not exists (select 1 from Registrars as strr where strr.event_id=2 and p.id=strr.person_id  )