SQL两个小技巧 -字符串切割和批量更新

1. 字符串切割 类似于Java中的split

select source_id order_no,
REGEXP_SUBSTR(a.product_name, '[^,]+', 1, l) goods_name,
REGEXP_SUBSTR(a.product_id, '[^,]+', 1, l) goods_sn,
REGEXP_SUBSTR(a.product_integral, '[^,]+', 1, l) integral_price,
REGEXP_SUBSTR(a.product_num, '[^,]+', 1, l) num
  from ebiz_point_yzh_exchange a, (SELECT LEVEL l FROM DUAL CONNECT BY LEVEL <= 100) b
 WHERE l <= LENGTH(a.product_name) - LENGTH(REPLACE(product_name, ',')) + 1
 order by source_id

2.批量更新

update bp_order as a left join 
(select  order_no,sum(integral_price * number) integral_num from bp_order_goods  GROUP BY order_no ) as  b  
on  a.order_no = b.order_no set a.integral_num = b.integral_num