mysql - 如何统计mysql的推荐量

Question

我有一个快速而肮脏的联盟系统。我有专栏

email
affiliate_id
recruiter_id

我现在想创建一个虚拟列来计算根据affiliate_id 招募了多少人。

所以我正在寻找的输出是

email
affiliate_id
total_recruited

示例数据是这样的

johndoe1@gmail.com aaaa 
johndoe2@gmail.com bbbb 
johndoe3@gmail.com cccc aaaa
johndoe4@gmail.com dddd aaaa
johndoe5@gmail.com eeee bbbb

查询结果应如下所示

johndoe1@gmail.com aaaa 2
johndoe2@gmail.com bbbb 1
johndoe3@gmail.com cccc 0
johndoe4@gmail.com dddd 0
johndoe5@gmail.com eeee 0

Answer 1

类似这样的东西吗？

SELECT email, affiliate_id, COALESCE(t1.tote_rec, 0) as total_recruited
FROM table t
LEFT JOIN
(   SELECT recruiter_id, COUNT(recruiter_id) as tote_rec
    FROM table
    GROUP BY recruiter_id
) t1 on t1. recruiter_id = t. affiliate_id

DEMO

您也可以不使用子查询来完成此操作，如下所示

SELECT t.email, t.affiliate_id, count(t1.recruiter_id) as total_recruited
FROM test_t t
LEFT JOIN test_t t1 on t1. recruiter_id = t. affiliate_id
GROUP BY t.affiliate_id;

另一个DEMO