怎样做平台推广_ppt模板官网_电商数据分析_河南百度seo

前言
由于本人sql比较薄弱 将一些常用又使用的sql语句记录与大家分享
一：替换特殊字符分割的字符串
需求如下：
有张原始表tschedule 和新表bjcodechange ，需要将原始表的compeny_code字段和bjcodechange 的oldwindcode 进行关联替换成bjcodechange表的newwindcode字段
无特殊字符的查询很简单 如下

UPDATE tschedule  tsSET ts.security_code =(SELECT bj.newwindcodeFROM BJcodechange bjWHERE ts.security_code = bj.oldwindcode)WHERE EXISTS (SELECT 1FROM BJcodechange bjWHERE ts.security_code = bj.oldwindcode);

但是有特殊字符的数据如下 注意测试原始表的compeny_code字段的内容许如下所示
tschedule：

name	compeny_code
张三	000001
李四	800981,000001,000002

bjcodechange：

oldwindcode	newwindcode
800981	888888

最终的效果如下

name	compeny_code
张三	000001
李四	888888 ,000001,000002

完整步骤
步骤1：拆分后的中间数据 (split_data)

rid	elem	ord
OKABKyAAA2AAAAiTAAD	000001	1
OKABKyAAA2AAAAiTAAD	800981	2
OKABKyAAA2AAAAiTAAD	000001	3
AABKyAAA2AAAAiTAAD	000002	1
步骤2：关联后的中间数据
rid	elem	newwindcode
----------------------	--------	-------------
OKABKyAAA2AAAAiTAAD	430017	920017
OKABKyAAA2AAAAiTAAD	000001	(null)
OKABKyAAA2AAAAiTAAD	430017	920017
AABKyAAA2AAAAiTAAD	000001	(null)
步骤3：聚合后的结果 (merged)
rid	new_code
----------------------	---------------------
OKABKyAAA2AAAAiTAAD	920017,000001,920017
AABKyAAA2AAAAiTAAD	000001

MERGE INTO tschedule a
USING (WITH split_data AS (SELECT a.rowid AS rid, regexp_substr(a.compeny_code, '[^,]+', 1, LEVEL) AS elem,LEVEL AS ordFROM tschedule aCONNECT BY LEVEL <= regexp_count(a.compeny_code, ',') + 1AND PRIOR a.rowid = a.rowidAND PRIOR DBMS_RANDOM.VALUE IS NOT NULL)SELECT rid,LISTAGG(NVL(b.newwindcode, elem), ',') WITHIN GROUP (ORDER BY ord) AS new_code FROM split_data sdLEFT JOIN bjcodechange b ON sd.elem = b.oldwindcode  GROUP BY rid
) merged
ON (a.rowid = merged.rid)
WHEN MATCHED THENUPDATE SET a.compeny_code = merged.new_code
WHERE a.compeny_code != merged.new_code;

好了 至此 oracle值sql记录 点点关注不迷路 老铁们！！！！！