1、表w_user结构:id,mobile,parent_id;
2、查询函数 queryChilds(uid)
CREATE FUNCTION `queryChilds`(`uid` varchar(32))
RETURNS longtext
BEGIN
DECLARE sTemp LONGTEXT;
DECLARE sTempChd LONGTEXT;
SET sTemp = '$';
SET sTempChd = uid;
WHILE sTempChd is not NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM w_user where FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
return sTemp;
END;
3、统计数据
-- SET GLOBAL group_concat_max_len = 102400;
SET SESSION group_concat_max_len = 102400; -- group_concat_max_len 长度修改
-- 查询下级
SET @uid := (
SELECT
id
FROM
w_user
WHERE
mobile = '15215166423'
);
SET @tempids := (
SELECT
queryChildrenUser (@uid)
);
SELECT
w1.mobile 手机号,
w2.mobile 上级,
FROM
w_user w1,
w_user w2
WHERE
w1.parent_id = w2.id
AND FIND_IN_SET(w1.id ,@tempids);