2 aibrooks aibrooks 于 2015.06.08 11:37 提问

mysql递归查询问题,折磨了很久 5C

mysql递归查询
用户表user
有字段 userid(主键) parentId(该用户上级id) position(用户所在位置只有“左、中、右”可选)。现在要查出当前用户的所有下级用户,并查出每个用户的位置有多少人:
效果:
图片说明
求解这条SQL怎么写。折磨了两个礼拜了

2个回答

zhanggd2015
zhanggd2015   2015.06.08 11:46

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

bulusli3
bulusli3   Ds   Rxr 2015.06.08 15:33

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

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);
aibrooks
aibrooks 这个例子我看过了 我也按照他的方法写了。是可以查询当前use的所有子集合。但是我没有办法做得到将 position (左中右)各个位置上的人数查询出来。
2 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片