mysql - 从2个表中获取有条件的行

我需要获得排名前3名的推荐人以及他的推荐人数，但只有合格的推荐人（table_2）。
我希望这是有道理的。到目前为止，我有以下查询。请帮忙。谢谢

SELECT count(*) as total_referrals, referrer
FROM table_1
WHERE table_2.qualified = '1'
GROUP BY referrer
ORDER BY total_referrals DESC
LIMIT 0,3

表格1

referrer   referral
user1      user89
user1      user54
user1      user23
user1      user56
user2      user89
user2      user23
user2      user45
user3      user78
user3      user14
user4      user10
user5      user98
user5      user56
...

table_2

referral   qualified
user89       1
user54       0
user23       0
user56       1
user89       1
user23       1
user45       0
user78       1
user14       1
user10       0
user98       1
user56       1
...

最佳答案

您需要在from子句中引用这两个表，并确定您的字段名称是引荐来源网址还是用户名：

SELECT count(table_1.referrals) as total_referrals, referrer
FROM table_1 left join table_2 on table_1.referrals=table_2.referrals
WHERE table_2.qualified = '1'
GROUP BY table_1.referrer
ORDER BY total_referrals DESC
LIMIT 0,3