一、查询某个节点下的所有子节点
CREATE FUNCTION getChildLst(rootId INT) RETURNS varchar(10000) BEGIN DECLARE sTemp VARCHAR(10000); DECLARE sTempChd VARCHAR(10000); SET sTemp = '$'; SET sTempChd =cast(rootId as CHAR); WHILE sTempChd is not null DO SET sTemp = concat(sTemp,',',sTempChd); SELECT group_concat(MODEL_ID) INTO sTempChd FROM TGIdm03 where FIND_IN_SET(PARENT_ID,sTempChd)>0; END WHILE; RETURN sTemp; END |
select getChildLst(1001)
使用FIND_IN_SET 函数来查询
select MODEL_ID from TGIDM03 where FIND_IN_SET(MODEL_ID, getChildLst(1001))
二、查找某个节点的父节点
CREATE FUNCTION getParList(rootId INT) RETURNS varchar(10000) BEGIN DECLARE sTemp VARCHAR(10000); DECLARE sTempPar VARCHAR(10000); SET sTemp = ''; SET sTempPar =rootId; WHILE sTempPar is not null DO IF sTemp != '' THEN SET sTemp = concat(sTemp,',',sTempPar); ELSE SET sTemp = sTempPar; END IF; SET sTemp = concat(sTemp,',',sTempPar); SELECT group_concat(PARENT_ID) INTO sTempPar FROM tgidm03 where PARENT_ID<>MODEL_ID and FIND_IN_SET(MODEL_ID,sTempPar)>0; END WHILE; RETURN sTemp; END |
select MODEL_ID from tgidm03 where FIND_IN_SET(MODEL_ID,getParList(196));