php - 在PHP MYSQL中寻找下线用户的顶线用户累积报告

Question

User Table
------------------------------------------
|userid |type   |reseller   |username   |
------------------------------------------
|1      |A      |1          |admin      |
|2      |R      |1          |user2      |
|3      |R      |1          |user3      |
|4      |C      |2          |user4      |
|5      |C      |2          |user5      |
|6      |C      |3          |user6      |
|7      |C      |3          |user7      |
------------------------------------------

A 代表管理员，R 代表经销商，C 代表客户

SMS Summary table
----------------------------------
|userid |totalsms   |date       |
----------------------------------
|2      |10         |13-01-2015 |
|3      |20         |13-01-2015 |
|4      |30         |13-01-2015 |
|5      |30         |13-01-2015 |
|6      |40         |13-01-2015 |
|7      |50         |13-01-2015 |
|2      |20         |13-01-2015 |
----------------------------------

需要结果

----------------------------------
|resellername   |totalsms       |
----------------------------------
|user2          |90             |
|user3          |110            |
----------------------------------

结果计算为

 userid2 = `10+20` + downline userid4 and userid5 = `30+30`
 userid3 = `20` + downline userid6 and userid7 = `40+50`

目前，为了实现这一点，我正在运行多个查询，例如，首先从短信摘要表中查询所有用户 ID，然后从用户表中获取特定经销商的用户名，然后对所有用户 ID 2 求和。

我想运行此查询一次而不是多次，我尝试了几种逻辑，但未能按我的要求实现。

请帮忙...提前致谢...

编辑:我一直尝试到这里，在那里我得到了个人报告，但没有通过 join

获得经销商报告

SELECT SUM(s.totalsms) AS totalsms, ru.username as reseller FROM smssummary s 
    left join  users u on s.userid=u.userid
    left join  users ru on ru.userid = (SELECT `reseller` FROM `users` WHERE `userid` = u.userid)

Answer 1

您可以对转销商进行汇总，然后对客户进行汇总，然后对总计进行汇总使用 left join 处理 reseller 没有任何客户的情况

SQL Fiddle Demo

select T.username as reseller, IFNULL(sum(S.totalsms)+smsCount,0) as TotalSMS
FROM
(
   SELECT U.userid, U.username, IFNULL(sum(SR.totalsms),0) as smsCount
   from Users U
   join SMSSummary SR
   on U.type ='R'
   and SR.userid = U.userid
   group by U.userid, U.username

) T
left join Users U
on U.type ='C' 
and U.reseller = T.userid
left join SMSSummary S
on S.userid = U.userid
group by T.username