确定“以下用户数”＆rdquo;在多层成员数据库中

select (case 
   when m1.userid is null then 0
   when m2.userid is null then 1
   when m3.userid is null then 2
   when m4.userid is null then 3
   when m5.userid is null then 4
   when m6.userid is null then 5
   when m7.userid is null then 6
   when m8.userid is null then 7
   else 8 end
   ) as users_below

from members as m0
left join members as m1 on m1.subof = m0.userid
left join members as m2 on m2.subof = m1.userid
left join members as m3 on m3.subof = m2.userid
left join members as m4 on m4.subof = m3.userid
left join members as m5 on m5.subof = m4.userid
left join members as m6 on m6.subof = m5.userid
left join members as m7 on m7.subof = m6.userid
left join members as m8 on m8.subof = m7.userid

where m0.userid = 1

select count(*) - 1 as users_below
from (
   select distinct u1, u2, u3, u4, u5, u6, u7
   from (
      select 
         m1.userid as u1, 
         m2.userid as u2, 
         m3.userid as u3,
         m4.userid as u4,
         m5.userid as u5,
         m6.userid as u6,
         m7.userid as u7

      from members as m0
      left join members as m1 on m1.subof = m0.userid
      left join members as m2 on m2.subof = m1.userid
      left join members as m3 on m3.subof = m2.userid
      left join members as m4 on m4.subof = m3.userid
      left join members as m5 on m5.subof = m4.userid
      left join members as m6 on m6.subof = m5.userid
      left join members as m7 on m7.subof = m6.userid

      where m0.userid = 1
      group by u1, u2, u3, u4, u5, u6, u7 with rollup
   ) d
) a

num_below + 1

userid | name  | subof
1      | John  | 0
2      | Joe   | 1
3      | Jill  | 0
4      | Janet | 2
5      | Dawn  | 4
6      | James | 4
7      | Mary  | 3
8      | Doug  | 6

userid  name    subof   
1           John    0
2           Joe     0.1
3           Jill    0
4           Janet   0.1.2
5           Dawn    0.1.2.4
6           James   0.1.2.4
7           Mary    0.3
8           Doug    0.1.2.4.6

    select * from temporary WHERE subof like '0.1.2%' ORDER BY userid ASC;
//this is joe's subof +'.'+ his userid

SELECT subof, userid 
FROM tablename 
WHERE name = 'doug'; #answer 0.1.2.4.6
$subof, $userid = mysql_fetch; //pseudo code

INSERT into tablename (userid, name, subof) VALUES ('9', 'Bill', '$subof.userid');

9   Bill    0.1.2.4.6.8

userid | name  | subof
1      | John  | 0
2      | Joe   | 0.1
3      | Jill  | 0
4      | Janet | 0.1.2
5      | Juan  | 0.1
6      | George| 0.1.2

    SELECT count(*) 
    FROM tablename 
    WHERE subof LIKE 'subof_of_any_given_user+that_users_userid%';
//get those under Joe by using '0.1.2' (Joe's subof + his userid)

//$juan_userid = 5
//$juan_subof = 0.1
//$updatevalue = $juan_subof.'.'.$juan_userid; //0.1.5

//$george_userid = 6
//$george_subof = 0.1.2
/$subofmatch = $george_subof.'.'.$george_userid; //0.1.2.6

UPDATE tablename 
SET subof = (REPLACE(subof, '$george_subof', '$updatevalue')) 
WHERE (subof like '$subofmatch%' OR userid = '$george_userid')



 // here it is with number values to make it easier to understand //  
    UPDATE tablename 
    SET subof = (REPLACE(subof, '0.1.2', '0.1.5')) 
    WHERE (subof like '0.1.2.6%' OR userid = '6');

userid  name    subof   
1           John    0
2           Joe     0.1
3           Jill    0
4           Janet   0.1.2
5           Juan    0.1
6           George  0.1.5

call employees_hier(1);

+-----------+
| num_below |
+-----------+
|         7 |
+-----------+
1 row in set (0.00 sec)

drop table if exists employees;
create table employees
(
emp_id smallint unsigned not null auto_increment primary key,
name varchar(255) not null,
boss_id smallint unsigned null,
key (boss_id)
)
engine = innodb;

insert into employees (name, boss_id) values
('f00',null), 
  ('ali later',1), 
  ('megan fox',1), 
      ('jessica alba',3), 
      ('eva longoria',3), 
         ('keira knightley',5), 
            ('liv tyler',6), 
            ('sophie marceau',7);

drop procedure if exists employees_hier;

delimiter #

create procedure employees_hier
(
in p_emp_id smallint unsigned
)
begin

declare v_done tinyint unsigned default(0);
declare v_dpth smallint unsigned default(0);

create temporary table hier(
 boss_id smallint unsigned, 
 emp_id smallint unsigned, 
 depth smallint unsigned
)engine = memory;

insert into hier select boss_id, emp_id, v_dpth from employees where emp_id = p_emp_id;

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table emps engine=memory select * from hier;

while not v_done do

    if exists( select 1 from employees e inner join hier on e.boss_id = hier.emp_id and hier.depth = v_dpth) then

        insert into hier select e.boss_id, e.emp_id, v_dpth + 1 
            from employees e inner join emps on e.boss_id = emps.emp_id and emps.depth = v_dpth;

        set v_dpth = v_dpth + 1;            

        truncate table emps;
        insert into emps select * from hier where depth = v_dpth;

    else
        set v_done = 1;
    end if;

end while;

select count(*) as num_below from hier where depth > 0;

/*
-- use this if you want to return the employees instead

select 
 e.emp_id,
 e.name as emp_name,
 p.emp_id as boss_emp_id,
 p.name as boss_name,
 hier.depth
from 
 hier
inner join employees e on hier.emp_id = e.emp_id
left outer join employees p on hier.boss_id = p.emp_id;
*/

drop temporary table if exists hier;
drop temporary table if exists emps;

end #

delimiter ;

-- call this sproc from your php

call employees_hier(1);
call employees_hier(2);
call employees_hier(3);
call employees_hier(5);
call employees_hier(6);
call employees_hier(7);