清华大佬耗费三个月吐血整理的几百G的资源,免费分享!....>>>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | CREATE TABLE `people` ( `id` INT (11) NOT NULL , ` name ` VARCHAR (50) NULL DEFAULT NULL , `pid` INT (11) NOT NULL DEFAULT '0' , PRIMARY KEY (`id`) ); CREATE DEFINER=`root`@`%` PROCEDURE `getChildren`( IN `parentId` INT ) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '获取某人所有后代' BEGIN #存放结果的临时表 DROP TABLE IF EXISTS children; CREATE TEMPORARY TABLE children SELECT 0 pLevel,p.* FROM `people` p WHERE id=parentId; #存放中间结果的临时表 DROP TABLE IF EXISTS tem; CREATE TEMPORARY TABLE tem SELECT id FROM `people` limit 0; #逐级填充后代 SET @pLevel=1; REPEAT #清空上次数据 TRUNCATE TABLE tem; #将当前 level 的后代id放入临时表 INSERT INTO tem SELECT p.id FROM `people` P, children c WHERE p.pid=c.id AND c.pLevel=(@pLevel-1); #将当前 level 的后代数据塞入结果临时表 INSERT INTO children SELECT @pLevel pLevel,p.* FROM `people` p, tem t WHERE p.id=t.id; SET @pLevel=@pLevel+1; UNTIL NOT EXISTS ( SELECT * FROM tem) OR @pLevel > 10 END REPEAT; #调整表结构,删除临时列和不需要的数据 ALTER TABLE children DROP COLUMN pLevel; DELETE FROM children WHERE id=parentId; #返回结果 SELECT * from children; END |