Mysql sql语句递归查询

1.今天接到任务要写一个sql来找出登陆人的部门层级关系，这是写完的sql，主要用到了sql递归查询，今天记录一下。

CREATE FUNCTION `GET_LX_DEPTNAME`(`loginId` bigint) RETURNS varchar(21845) CHARSET utf8
BEGIN
DECLARE sTemp_level VARCHAR(500);
DECLARE sTemp_id VARCHAR(500);
DECLARE sTemp_name VARCHAR(500);
DECLARE lx_dept_id VARCHAR(500);
DECLARE lx_dept_name VARCHAR(21845);
DECLARE sTemp_count int;

#先判断登录人
select A.GROUP_LEVEL ,A.PARTY_ID,A.GROUP_NAME into sTemp_level ,sTemp_id ,sTemp_name — ,A.GROUP_NAME,B.PARTY_ID_FROM AS HR_ID
from HR_ORGANIZATION a,COM_PARTY_RELATIONSHIP b,ep_r_user_login c
where A.PARTY_ID = B.PARTY_ID_FROM AND B.PARTY_ID_TO = C.PARTY_ID
AND B.RELATIONSHIP_TYPE_ID = 3 and b.thru_date > sysdate() and a.THRU_DATE > sysdate()
and (b.PRIORITY_TYPE_ID=0 or b.PRIORITY_TYPE_ID is null) — 任职部门
and c.LOGIN_ID = loginId;

set sTemp_count:=0;
#获取工程系统
set lx_dept_id:=sTemp_id;
set lx_dept_name:=sTemp_name;
WHILE (sTemp_id <> 3 or sTemp_level is null) and sTemp_count<20 DO — parytid =3说明是总公司

set sTemp_count:=sTemp_count+1;

SELECT C.PARTY_ID,ifnull(GROUP_LEVEL,1) ,C.GROUP_NAME into sTemp_id ,sTemp_level ,sTemp_name
FROM HR_ORGANIZATION C ,COM_PARTY_RELATIONSHIP B
where B.PARTY_ID_TO = sTemp_id
AND B.RELATIONSHIP_TYPE_ID = 1
AND C.FROM_DATE <= sysdate() AND C.THRU_DATE >= sysdate()
AND B.FROM_DATE <= sysdate() AND B.THRU_DATE >= sysdate()
AND B.PARTY_ID_FROM = C.PARTY_ID;
# set lx_dept_id:=CONCAT_WS(‘,’,lx_dept_id,sTemp_id);
set lx_dept_name:=CONCAT_WS(‘-‘,sTemp_name,lx_dept_name);
# set lx_dept_name:=sTemp_name;
END WHILE;

return lx_dept_name;

END