Oracle字符串拼接循环拼接

最新推荐文章于 2023-12-29 20:04:24 发布

盒子家

最新推荐文章于 2023-12-29 20:04:24 发布

阅读量1.1k

点赞数

本文链接：https://blog.youkuaiyun.com/liufeihuwudi/article/details/112475048

版权

博客展示了一段SQL查询代码，先通过CTE（Common Table Expression）创建临时表temp，从COPRODUCT_INFO表筛选特定条件的数据并处理WAFER_LIST字段。后续又进行了进一步的查询操作，涉及字符串处理和连接，以获取所需结果。

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

`` 在这里插入图片描述
`
with temp as (select replace(replace( replace(aa.WAFER_LIST,’[’,’’),’]’,’’),’"’,’’) waferlist,CUSTOMER_LOT_ID
from (SELECT OBJECT_RRN,
COPRODUCT_RRN,
COPRODUCT_ID,
MAIN_PRODUCT_RRN,
MAIN_PRODUCT_ID,
WORKORDER_RRN,
WORKORDER_ID,
CROSS_DIE,
COOD_DIE,
WAFER_QTY,
WAFER_LIST,
CUSTOMER_LOT_ID
FROM COPRODUCT_INFO
WHERE 1 = 1
AND COPRODUCT_RRN = 5245667
AND WORKORDER_RRN = 5245668
) aa)

**SELECT --t1.rrn,
CUSTOMER_LOT_ID || ‘#’ ||substr(t1.waferlist,
instr(t1.waferlist, ‘,’, 1, t2.lvl) + 1,
instr(t1.waferlist, ‘,’, 1, t2.lvl + 1) -
(instr(t1.waferlist, ‘,’, 1, t2.lvl) + 1))

FROM (SELECT --t.rrn,
CUSTOMER_LOT_ID,
‘,’ || t.waferlist || ‘,’ waferlist,
length(t.waferlist) - nvl(length(REPLACE(t.waferlist, ‘,’)), 0) + 1 AS cnt
FROM temp t
WHERE 1=1
–and rrn = 22222
) t1,
(SELECT rownum AS lvl
FROM (SELECT MAX(length(t.waferlist || ‘,’) -
nvl(length(REPLACE(t.waferlist, ‘,’)), 0)) max_len
FROM temp t
WHERE 1=1
–and rrn = 22222
)
CONNECT BY LEVEL <= max_len) t2
WHERE 1=1

AND t2.lvl <= t1.cnt**