使用WITH语句, [MySQL Recursive CTE (Common Table Expressions)](https://www.geeksforgeeks.org/mysql-recursive-cte-common-table-expressions/)
oracle:
SELECT P.SID, P.PRJ_NAME
FROM TB_SYS_PROJECT P
CONNECT BY P.PARENT_SID = PRIOR P.SID
START WITH EXISTS (SELECT 1
FROM TB_SYS_ROLE R
LEFT JOIN TB_SYS_USER_TO_ROLE UR
ON UR.ROLE_SID = R.SID
WHERE R.PROJECT_SID = P.SID
AND UR.USER_SID = #{userSid})
mysql:
WITH RECURSIVE CTE AS
(
SELECT P.SID, P.PRJ_NAME FROM TB_SYS_PROJECT P LEFT JOIN TB_SYS_ROLE R ON P.SID = R.PROJECT_SID LEFT JOIN TB_SYS_USER_TO_ROLE UR ON R.SID = UR.ROLE_SID WHERE UR.USER_SID = #{userSid}
UNION ALL
SELECT P.SID, P.PRJ_NAME FROM TB_SYS_PROJECT P JOIN CTE ON P.PARENT_SID = CTE.SID
)
SELECT SID, PRJ_NAME FROM CTE;
INSTR
LOCATE
INSTR4
DELIMITER //
CREATE FUNCTION INSTR4 (p_str VARCHAR(8000), p_substr VARCHAR(255), p_start INT, p_occurrence INT) RETURNS INT DETERMINISTIC BEGIN DECLARE v_found INT DEFAULT p_occurrence; DECLARE v_pos INT DEFAULT p_start;
lbl:
WHILE 1=1
DO
-- Find the next occurrence
SET v_pos = LOCATE(p_substr, p_str, v_pos);
-- Nothing found
IF v_pos IS NULL OR v_pos = 0 THEN
RETURN v_pos;
END IF;
-- The required occurrence found
IF v_found = 1 THEN
LEAVE lbl;
END IF;
-- Prepare to find another one occurrence
SET v_found = v_found - 1;
SET v_pos = v_pos + 1;
END WHILE;
RETURN v_pos; END; //
DELIMITER ;