MySQL:在一个表中查找行而不是另一个表，同时找到两个

Question

我正在努力了解 MySQL 连接。我有三张 table

-- events
id  name
1   Event 1
2   Event 2

--  registrations
id   event  name
1    1      Alice
2    1      Bob
3    2      Alice
4    2      Charlie

-- scores
id  event  name     score
1   1      Alice    10
2   1      Charlie  20
3   2      Alice    15
4   2      Bob      30

对于我正在尝试解决的每个事件

• 有多少人注册(注册表中的行)但没有得到分数(不包括分数表中的行)
• 有多少人得到分数(分数表中的行)但没有注册(不包括注册表中的行)
• 有多少人同时注册并获得了分数

我尝试过不同的变体

SELECT  *
FROM    registrations r
LEFT JOIN scores s
ON      r.event = s.event
WHERE   s.event IS NULL
AND r.event = 1

但我不确定我应该加入什么:event 或 name 但两者都不是 null 而且我似乎从来没有得到我正在寻找的正确数字为了。最后的结果应该是这样的

      name     reg_only score_only reg&score total 
event Event 1  1        1          1         3

Answer 1

您可以按照您在查询中提到的相同顺序使用以下 3 个查询-

SELECT e.id, e.name, COUNT(r.id) AS registered_user
FROM `events` AS e
INNER JOIN registrations AS r ON e.id=r.event 
LEFT JOIN scores AS s ON e.id = s.event
WHERE   s.event IS NULL;

SELECT e.id, e.name, COUNT(r.id) AS scored_user
FROM `events`scores AS e
INNER JOIN scores AS s ON e.id=s.event 
LEFT JOIN registrations AS r ON e.id = r.event
WHERE   r.event IS NULL;

SELECT e.id, e.name, COUNT(*) AS both_user
FROM `events` AS e
INNER JOIN registrations AS r ON e.id=r.event 
INNER JOIN scores AS s ON e.id = s.event;