mysql - 选择三个表并计数

Question

我有三个表，我需要选择email，统计A表和B表的关系，例如:

表A:

 ID   |  email
 1    |  test@test
 2    |  test2@test
 3    |  test3@test

表 B:

UID   | username
11    | James
22    | Gabriel
33    | Jonas

表C:(A和B之间的关系)

  ID | email_id  | username_id
  1  |  1        | 11
  2  |  1        | 22
  3  |  2        | 33

预期结果:

Email      | Totalrelation
test@test  | 2
test2@test | 1

我试过:

   select tableA.email, 
    COUNT(distinct tableC.email_id) AS total from tableA as tableA, tableC as tableC GROUP BY tableC.email_id

但它没有用，我完全错了。我该怎么做？

Answer 1

加入表格，对数据进行分组并计算每组的数量

select a.email, count(c.id) as cnt
from tableA a
left join tableC c on c.email_id = a.id
group by a.email