只需要一条sql语句 根据上级pid去查询，下级手机号，和该下级对应的下级总数量。怎么查，求大神告知

Question

Answer 1

如果你的下级的总数量不是那种： 下级及下级的下级及下级的下级的下级....的话这样写就可以了

SELECT
    emp.username,
    chc.count 
FROM
    tb_employee emp
    ,(
    SELECT
        pid,
        count( 1 ) count 
    FROM
        tb_employee 
    WHERE
        pid IN ( SELECT id FROM tb_employee d WHERE pid = 1 ) 
    GROUP BY
    pid 
    ) chc where emp.id = chc.pid

改成：

SELECT
    emp.id,
    emp.username,
    IFNULL(chc.count,0) 
FROM
    tb_employee emp
    LEFT JOIN (
    SELECT
        pid,
        count( 1 ) count 
    FROM
        tb_employee 
    WHERE
        pid IN ( SELECT id FROM tb_employee d WHERE pid = 1 ) 
    GROUP BY
    pid 
    ) chc ON emp.id = chc.pid
    where emp.pid = 1