oracle 2个数组列,剔除数组重复的数据。
一、下面这样不规则的,数据如何剔除掉。循环筛选replace替换掉。
序号 正常时间 剔除时间
1 2022-12-19,2022-12-20,2022-12-21,2022-12-22,2022-12-23 2022-12-19,2022-12-23
2 2022-12-21,2022-12-22,2022-12-23,2022-12-24,2022-12-25 2022-12-23,2022-12-24,2022-12-25
二、得到的结果。
序号 剔除后的时间 剔除时间
1 2022-12-20,2022-12-21,2022-12-22 2022-12-19,2022-12-23
2 2022-12-21,2022-12-22 2022-12-23,2022-12-24,2022-12-25
三、存储函数。
create or replace function F_WORKTIME(p_StartDate varchar2,p_EndDate varchar2, List varchar2) return worktime_Table PIPELINED as v WorkTime_ROW; id number; worktime DATE; stopworktime DATE; p_StopTimevarray VARCHAR2(100); stoparrays varchar2(200); workarraysAll VARCHAR2(100); CURSOR cur IS select t.c0084_id as id, t.c0084_worktime as worktime, t.c0084_stopworktime as stopworktimefrom t0084_apply_zy t inner join t0080_buildproject_zy t1 on t1.c0080_pjt_id=t.c0080_pjt_id inner join t0003_area t2 on t2.t0003_areaid=t1.c0003_stcode inner join T0511_USERINFO t0511 on t1.c0080_logid=t0511.c0511_logid where (t.c0084_state=7 or t.c0084_state=12 or t.c0084_state=14) and p_StartDate>=substr(c0084_worktime,0,10) and p_EndDate<=substr(c0084_worktime,instr(c0084_worktime,',',-1,1)+1,10) and t2.t0003_areaid in (select * from table(strtab(to_char(List)))); CURSOR curstop IS select regexp_substr(p_StopTimevarray,'[^,]+',1,level) as stoparrays from dual connect by level<=length(p_StopTimevarray)-length(replace(p_StopTimevarray,','))+1; begin for temp in cur loop p_StopTimevarray:=temp.stopworktime; workarraysAll:=temp.worktime; for tempstop in curstop loop if tempstop.stoparrays is not null then workarraysAll:=replace(concat(workarraysAll,','),concat(tempstop.stoparrays,',')); end if; end loop ; v := WorkTime_ROW(temp.id,trim(trailing ',' from workarraysAll)); PIPE ROW (v); end loop; RETURN; end F_WORKTIME;