如何计算引荐数 mysql [英] How to count referalls mysql

问题描述

我有一个快速而肮脏的会员系统.我有专栏

I have a quick and dirty affiliate system. I have the columns

email
affiliate_id
recruiter_id

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

I want to now create a virtual column to count how many people were recruited based on affiliate_id.

所以我要找的输出是

email
affiliate_id
total_recruited

样本数据是这样的

[e-mail] aaaa 
[e-mail] bbbb 
[e-mail] cccc aaaa
[e-mail] dddd aaaa
[e-mail] eeee bbbb

查询结果应该是这样的

[e-mail] aaaa 2
[e-mail] bbbb 1
[e-mail] cccc 0
[e-mail] dddd 0
[e-mail] eeee 0

推荐答案

这样的事情?

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

演示

你也可以不用像这样的子查询

you can also do it without a subquery like so

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

这篇关于如何计算引荐数 mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！