秘书姐姐说想知道她发起的所有流程，现在都到谁审批了。由于一条流程当前审批人可能有多个，故需根据单据编号（djbh）将审批人拼接到一个字段中。
说明：
wfn审批历史记录表，djbh 单据编号，pk_check审批人主键
user 用户表，uname 姓名，ucode 账号, pk_user 用户主键
方法一：使用SYS_CONNECT_BY_PATH
SELECT djbh, LTRIM(MAX(SYS_CONNECT_BY_PATH(uname, ' ')), ',') appname,LTRIM(MAX(SYS_CONNECT_BY_PATH(ucode, ' ')), ',') appcode
FROM (SELECT wf.pk_check,user.ucode,user.uname,wf.djbh, ROW_NUMBER() OVER(PARTITION BY wf.djbh ORDER BY wf.pk_check DESC) RN
FROM wfn wf
left join user on wf.pk_check= user.pk_user
where checknote is null and approvestatus = '')
START WITH RN = 1
CONNECT BY RN - 1 = PRIOR RN
AND djbh = PRIOR djbh
GROUP BY djbh;
方法二：使用wm_concat
select djbh,dbms_lob.substr(wm_concat(uname)) appname,dbms_lob.substr(wm_concat(ucode)) appcode
from (SELECT user.ucode,user.uname,wf.djbh
FROM wfn wf
left join user on wf.pk_check= user.pk_user
where checknote is null and approvestatus = '')
group by djbh;
2018-08-1414:53:28