秘书姐姐说想知道她发起的所有流程，现在都到谁审批了。由于一条流程当前审批人可能有多个，故需根据单据编号（djbh）将审批人拼接到一个字段中。

说明：

wfn审批历史记录表，djbh 单据编号，pk_check审批人主键

user 用户表，uname 姓名，ucode 账号, pk_user 用户主键

 

方法一：使用SYS_CONNECT_BY_PATH

1 SELECT djbh, LTRIM(MAX(SYS_CONNECT_BY_PATH(uname, ' ')), ',') appname,LTRIM(MAX(SYS_CONNECT_BY_PATH(ucode, ' ')), ',') appcode
2 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
3 FROM wfn wf
4 left join user on wf.pk_check= user.pk_user
5 where checknote is null and approvestatus = '0')
6 START WITH RN = 1
7 CONNECT BY RN - 1 = PRIOR RN
8 AND djbh = PRIOR djbh
9 GROUP BY djbh;

 

方法二：使用wm_concat

1 select djbh,dbms_lob.substr(wm_concat(uname)) appname,dbms_lob.substr(wm_concat(ucode)) appcode
2 from (SELECT user.ucode,user.uname,wf.djbh
3 FROM wfn wf
4 left join user on wf.pk_check= user.pk_user
5 where checknote is null and approvestatus = '0')
6 group by djbh;

 

2018-08-1414:53:28