Oracle分割

最新推荐文章于 2025-04-22 15:28:26 发布

liuyaru11

最新推荐文章于 2025-04-22 15:28:26 发布

阅读量1.9k

点赞数

本文链接：https://blog.youkuaiyun.com/liuyaru11/article/details/106685249

版权

本文分享了两种在Oracle数据库中高效分割批量包含逗号的订单号的方法。方法一使用REGEXP_SUBSTR与LEVEL结合，逐级提取订单号。方法二则通过SUBSTR与INSTR函数，配合ROWNUM循环，统计特定日期区间内每天处理的独立订单数量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle 分割

方法1

select T.DHPAY_ID,
T.ORDERNO,
trim(REGEXP_SUBSTR(ORDERNO, ‘[^,]+’, 1, level)) ORDERNO
from (select d.DHPAY_ID, d.ORDERNO
from FUND_PAYMENT_DHPAY d
where d.response_code = ‘01’
and d.insertdate >= to_date(‘2019-06-01’, ‘yyyy-mm-dd’)) T
connect by INSTR(ORDERNO, ‘,’, 1, level - 1) > 0；

方法2

select to_char(t.insert_date, ‘yyyy-mm-dd’) as dateno,
count(distinct substr(t.order_no,
instr(’,’ || t.order_no, ‘,’, 1, rn),
instr(t.order_no || ‘,’, ‘,’, 1, rn) -
instr(’,’ || t.order_no, ‘,’, 1, rn))) payfororder
from (select p.ORDERNO order_no, p.insertdate insert_date, b.rn
from FUND_PAYMENT_DHPAY p,
(select rownum rn from dual connect by rownum <= 100) b
where instr(’,’ || p.orderno, ‘,’, 1, rn) > 0
and p.insertdate >= to_date(‘2020-06-01’, ‘yyyy-mm-dd’)
and p.insertdate < to_date(‘2020-06-11’, ‘yyyy-mm-dd’)) t
group by to_char(t.insert_date, ‘yyyy-mm-dd’)
order by to_char(t.insert_date, ‘yyyy-mm-dd’);