1.Oracle中REGEXP_SUBSTR函数:
在oracle中某字段数值为用逗号(,)隔开的值,如图所示
现在需要将这条数据拆分成两条数据,其它字段显示值不变都一样,
select cc.*,
tt.name tname,
tt.encrypt_telphone,
tt.encrypt_name,
tt.encrypt_address
from (select c.*, REGEXP_SUBSTR(c.task_id, '[^,]+', 1, l) AS taskid
from ep_crm_customer_shlgxx c,
(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL <= 30)
WHERE l <= LENGTH(c.task_id) - LENGTH(REPLACE(c.task_id, ',')) + 1) cc
left join ep_crm_task tt
on cc.task_id = tt.id
where 1 = 1
and cc.assign = 1
and cc.agent_id = '10001'
order by cc.assign_time desc, cc.id desc
得到结果
页面显示:
2.Oracle中lead over, lag over 函数:
获取数据的上一条或下一条数据,根据某字段order by
select n.next_cust_id
from (select cw.id,
lead(cw.id, 1, 0) over(order by cw.assign_time desc, cw.id desc) as next_cust_id,
t.name tname,
cw.name,
cw.telephone,
cw.mobile,
cw.coment,
cw.intent,
cw.create_time,
cw.dial_time
from ep_crm_customer_shlgxx cw
left join ep_crm_task t
on cw.task_id = t.id
where 1 = 1
and cw.assign = 1
and cw.agent_id = '10001') n
where n.id = 'ee27acf7-83c0-45bc-924c-a624915b18c2'
执行结果如下图所示: