MySQL树状数据的数据库设计

SELECT ancestor_path FROM tmp_rOrganIdList WHERE organ_id=intPOrganId INTO strPPath;

INSERT INTO tmp_cOrganIdList (organ_id) (SELECT organ_id FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId);-- 次级机构id的缓存区

-- SELECT COUNT(1) FROM tmp_pOrganIdList INTO intDelCount;

INSERT INTO tmp_rOrganIdList (organ_id,p_organ_id,ancestor_path)

(SELECT organ_id,intPOrganId,CONCAT(strPPath,intPOrganId,',') FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId);

DELETE FROM tmp_aOrganIdList WHERE p_organ_id=intPOrganId;

SET intPIndex=intPIndex+1;

END WHILE;

DELETE FROM tmp_pOrganIdList;

IF (SELECT COUNT(1) FROM tmp_cOrganIdList)>0 THEN

INSERT INTO tmp_pOrganIdList (organ_id) (SELECT organ_id FROM tmp_cOrganIdList);

DELETE FROM tmp_cOrganIdList;

ELSE

SET intLoopDone=1;

END IF;

-- SELECT * FROM tmp_pOrganIdList;

-- SELECT COUNT(1) FROM tmp_aOrganIdList;

-- SELECT intLoopDone;

END WHILE;

-- SELECT * FROM tmp_rOrganIdList;-- 想要查看测试的结不雅,请看此表

　　推荐阅读

　　每个程序员都该知道的五大定律

定律-或称轨则，可以指导我们并让我们在错误的缺点中进修。这篇文┞仿中，我将介绍我每次设计或实现软件时涌如今我脑海的五大年夜定律。个中有些和开辟有关，有些和体系组织有关。它们可以赞助你成为合>>>详细阅读

地址：http://www.17bianji.com/lsqh/37612.html