mysql递归查询
mysql递归查询
对于数据库中的树形结构数据,如部门表,有时候,我们需要知道某部门的所有下属部分或者某部分的所有上级部门,这时候就需要用到mysql的递归查询
1.表结构
2.向上递归查询
CREATE FUNCTION queryParentId(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp='$';
SET sTempChd = CAST(areaId AS CHAR);
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT pid INTO sTempChd FROM department WHERE id = sTempChd;
WHILE sTempChd <> -1 DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT pid INTO sTempChd FROM department WHERE id = sTempChd;
END WHILE;
RETURN sTemp;
END;
3.使用
SELECT * from department where FIND_IN_SET(id,queryParentId(4));