56,866
社区成员




CREATE DEFINER=`root`@`%` PROCEDURE `Proc_GetMySub`(IN `InUid` int)
label_pro:BEGIN
DECLARE userCode int DEFAULT 0;
SELECT mycode into userCode FROM tb_xx;
-- 建一个临时表
DROP TABLE IF EXISTS tbTem_userId;
create table tbTem_userId(uid INT);
-- 我的第一下级
INSERT INTO tbTem_userId SELECT uid FROM tb_xx WHERE pcode = userCode;
-- 我的第二下级
INSERT INTO tbTem_userId SELECT uid FROM tb_xx WHERE pcode IN (SELECT uid FROM tb_xx WHERE pcode = userCode);
-- 查询出所有的下级
SELECT uid FROM tbTem_userId;
END
如果要查询多个下级一次类推,不过不建议下级超过5级,即使用其他的递归查询也是效率极低,如果非要用多级的话建议楼主根据应用场景重新设计表以便达到较好的查询效率