[大数据]MySQL 获取祖父节点，子节点

函数获取祖父节点

CREATE DEFINER=`root`@`%` FUNCTION `getDeptParentList`(`rootId` varchar(64)) RETURNS varchar(1000) CHARSET utf8
BEGIN

DECLARE nParentList VARCHAR(10000);
DECLARE nParentTemp VARCHAR(10000);

SET nParentTemp = rootId;

WHILE nParentTemp IS NOT NULL DO
IF (nParentList IS NOT NULL) THEN
SET nParentList = CONCAT(nParentTemp,',',nParentList);
ELSE
SET nParentList = CONCAT(nParentTemp);
END IF;

SELECT GROUP_CONCAT(parent_id) INTO nParentTemp FROM sys_dept WHERE FIND_IN_SET(id,nParentTemp)>0;
END WHILE;
RETURN nParentList;
END

获取子节点

-- 获取子级（包括自己）
SELECT
	*
FROM
	hr_sys_menu
WHERE
	menu_id IN (
		SELECT menu_id
		FROM
			hr_sys_menu
		WHERE menu_id = '1'
		UNION
			(
				SELECT menu_id
				FROM
					( SELECT * FROM hr_sys_menu ORDER BY parent_id, menu_id ) depart_sorted,
					(SELECT @pv := '1') initialisation
				WHERE
					find_in_set(parent_id ,@pv)
				  AND length( @pv := concat(@pv, ',', menu_id) )
			)
	);

-- 获取子级（不包括自己）
SELECT
	*
FROM
	hr_sys_menu
WHERE
	menu_id IN (
		
		SELECT menu_id
		FROM
			( SELECT * FROM hr_sys_menu ORDER BY parent_id, menu_id ) depart_sorted,
			(SELECT @pv := '1') initialisation
		WHERE
			find_in_set(parent_id ,@pv)
			AND length( @pv := concat(@pv, ',', menu_id) )
			
	);

-- 获取下一级
SELECT
	*
FROM
	hr_sys_menu
WHERE
	menu_id IN (
		SELECT menu_id
		FROM
			( SELECT * FROM hr_sys_menu ORDER BY parent_id, menu_id ) depart_sorted,
			(SELECT @pv := '1') initialisation
		WHERE
			find_in_set(parent_id ,@pv)
			
	);

-- 获取父节点（包括自己）
SELECT  T2.* FROM (
		SELECT
			 @R AS _ID,
			 (SELECT @R := parent_id FROM hr_sys_menu WHERE menu_id = _ID) AS PAR_ID,
			 @L := @L + 1 AS LVL
		FROM
		(SELECT @R := '4028808a6cb8383c016cb8388f0e0001', @L := 0) VARS,
		hr_sys_menu H
		WHERE @R <> 0
) T1
INNER JOIN hr_sys_menu T2 ON T1._ID = T2.menu_id 
ORDER BY T1.LVL DESC;



-- 获取父节点（不包括自己）
SELECT  T2.* FROM (
		SELECT
			 @R AS _ID,
			 (SELECT @R := parent_id FROM hr_sys_menu WHERE menu_id = _ID) AS PAR_ID,
			 @L := @L + 1 AS LVL
		FROM
		(SELECT @R := '4028808a6cb8383c016cb8388f0e0001', @L := 0) VARS,
		hr_sys_menu H
		WHERE @R <> 0
) T1
INNER JOIN hr_sys_menu T2 ON T1._ID = T2.menu_id and T2.menu_id !='4028808a6cb8383c016cb8388f0e0001'
ORDER BY T1.LVL DESC;