mysql - SQL：使用推荐代码从表中提取已验证用户

Question

我有一个与获取已验证用户总数有关的查询。

我有一个名为table1的表。

表：表1

id    name      code    status    refer_from_code
1     Name1     R1      1         0
2     Name2     R2      0         R4
3     Name3     R3      1         R2
4     Name4     R4      0         R4
5     Name5     R5      0         R2
6     Name6     R6      1         R1
7     Name7     R7      1         R6

期望的结果：

id    name      total_referred     total_referred_verified
1     Name1     1                  1
2     Name2     2                  1
3     Name3     0                  0
4     Name4     2                  0
5     Name5     0                  0
6     Name6     1                  1
7     Name7     0                  0

我现在所做的：

我已经编写了以下查询来执行此任务，但它没有按应有的方式工作。

SELECT
    u.name,
    IFNULL(referrals.total_referred, 0) total_referrals
FROM users u
LEFT JOIN (
    SELECT
        ui.refer_from_code,
        COUNT(*) AS total_referred
    FROM users ui
    GROUP BY ui.refer_from_code
) AS referrals ON referrals.refer_from_code = u.code

我想使用单个 MySQL 查询来做到这一点。

标签： mysql

Answer 1

你可以这样Common Table Expression写

with cte as (
select refer_from_code , 
count(*) as total_referred,
sum(status) as total_referred_verified
from users where refer_from_code!='0' group by refer_from_code
)

select 
t1.id,
t1.name,
coalesce(t2.total_referred,0) as total_referred,
coalesce(t2.total_referred_verified,0) as total_referred_verified
from 
users t1 left join cte t2 on t1.code=t2.refer_from_code

演示