mysql 递归表查询 mysql递归查询语句

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