mysql递归查询问题，折磨了很久

mysql递归查询
用户表user
有字段 userid（主键） parentId（该用户上级id） position（用户所在位置只有“左、中、右”可选）。现在要查出当前用户的所有下级用户，并查出每个用户的位置有多少人：
效果：

求解这条SQL怎么写。折磨了两个礼拜了

with tb1 as
(
select c.userid,c.parentId,0 as lv1 from user c where c.Number=67
union all
select c.userid,c.parentId,lv1-1 from tb1 z
inner join user c
on z.parentId=c.userid
)
select tb1.userid ,tb1.parentId,tb1.lv1 as lv from tb1

用过存储过程吗？可以使用存储过程来做，以下是一个类似的寻找子节点的例子，可以直接运行的：

CREATE TABLE user (
userid int(10),
parentId int(10),
total INT(10)
);

insert into user values
(1001,null,50),
(1002,1001,400),
(1003,1001,500),
(1004,1002,600),
(1005,1002,100),
(1006,1002,200),
(1007,1003,300),
(1008,1003,8600);

drop procedure if exists getChildList;
delimiter //

CREATE procedure getChildList(in currUserId INT)
BEGIN
drop table if exists temp;
CREATE TABLE temp (
userid INT(10),
total INT(10)
);

    drop table if exists temp1;

CREATE TABLE temp1 (
userid INT(10),
total INT(10)
);

INSERT temp (select userid,total from user where parentId=currUserId);
INSERT temp1 (select userid,total from user where parentId=currUserId);

  WHILE row_count()>0 DO

          drop table if exists temp2;

CREATE TABLE temp2 (
userid INT(10),
total INT(10)
);

insert temp1 (select u.userid,u.total from user as u inner join temp as t on u.parentId=t.userid);
insert temp2 (select u.userid,u.total from user as u inner join temp as t on u.parentId=t.userid);
DELETE FROM temp;
insert temp (select * from temp2);
DELETE FROM temp2;
END WHILE;

SELECT
*
FROM
temp1;

END;
//

call getChildList(1003);