php - MySQL:获取推荐人花费最多的前 3 个推荐人

Question

我需要获得推荐人花费最多的前 3 个推荐人。我需要获得推荐人的数量和推荐人花费的总金额。是否可以在 1 个查询中完成它？到目前为止，我有这个查询，但我不确定我会遗漏什么。请帮忙!谢谢!

SELECT SUM(t2.payment) as total_payments, t2.username as referral, t1.referrer as referrer
FROM t1, t2 
WHERE t1.username = t2.username  
GROUP BY referrer
ORDER BY total_payments DESC 
LIMIT 0,10

t1(用户数据，约 1000 条记录)

username   referrer 
user1       user67
user2       user89
user3       user89  
user4       user57
user5       user57
user6       user89
user7       user67
user8       user89
...

t2(用户付款，约 300 条记录)

username    payment
user1        100
user1        200
user2        350
user1         50
user5        500
user1        200
user3        150
user2         80
...

Answer 1

下面的查询应该会给你正确的结果:

SELECT t1.referrer as referrer, count(t2.username) as referral, SUM(t2.payment) as total_paymentsFROM t1, t2 WHERE t1.username = t2.username  GROUP BY referrerORDER BY total_payments DESC LIMIT 0,10