1語(yǔ)句中定義變量
SELECT
*
FROM
department
WHERE
id IN(
SELECT id
FROM department
WHERE id = 4
UNION(
SELECT id FROM
(
SELECT id,parent_id
FROM department
ORDER BY
parent_id,id
) depart_sorted,
(SELECT @pv := 4)
initialisation
WHERE find_in_set(parent_id,@pv)
AND length(@pv:=concat(@pv,',',id))
)
);
tips:如果id為字符串可能會(huì)出現(xiàn) Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) 錯(cuò)誤
可以在find_in_set(parent_id,@pv 后添加?COLLATE utf8mb4_unicode_ci
2寫數(shù)據(jù)庫(kù)函數(shù)通過函數(shù)查詢
函數(shù)
create function getRoles(roleId INT)
returns varchar(4000)
BEGIN
DECLARE oTemp VARCHAR(4000);
DECLARE oTempChild VARCHAR(4000);
SET oTemp = '';
SET oTempChild = CAST(roleId AS CHAR);
WHILE oTempChild IS NOT NULL
DO
SET oTemp = CONCAT(oTemp,',',oTempChild);
SELECT GROUP_CONCAT(id) INTO oTempChild FROM yzm_talks_role WHERE FIND_IN_SET(parent_id,oTempChild) > 0;
END WHILE;
RETURN oTemp;
END
調(diào)用
SELECT *
FROM yzm_talks_role
WHERE FIND_IN_SET(parent_id,getRoles(#{id}))
3程序內(nèi)處理
查詢id,parant_id到程序中,通過程序處理
獲取樹形結(jié)構(gòu)所有子節(jié)點(diǎn)
oracle中有connect by prior可以使用
