我有三个表,我需要选择电子邮件并计算表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
但它没有用,我完全错了。我该怎么做?
连接表,对数据进行分组并计算每个组的数量
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