求各位帮忙看看,表中某个字段值很乱,想抽取有用的信息出来并做些计算
如:
id value
1 _id:$oid:573e5bb44d89070f00cf65e9,types:20 GP,_id:$oid:573e5bb44d89070f00cf65e8,types:40 HQ
2 _id:$oid:5758a988c74d490f0093a2bc,types:
3 qty:2,_id:$oid:573d7e214d89070f00ce5e60,types:20 GP,qty:3,_id:$oid:573d7e214d89070f00ce5e5f,types:40 HQ--------------------------------------------------------------
想抽取的结果是
id qty* types**
1 0 20GP, 40HQ
2 0
3 5 20GP, 40HQ*qty: 字段中所有的qty相加 (不确定有几个qty)
**types: 字段中所有types值串连 (同不确定有几个types)
如:
id value
1 _id:$oid:573e5bb44d89070f00cf65e9,types:20 GP,_id:$oid:573e5bb44d89070f00cf65e8,types:40 HQ
2 _id:$oid:5758a988c74d490f0093a2bc,types:
3 qty:2,_id:$oid:573d7e214d89070f00ce5e60,types:20 GP,qty:3,_id:$oid:573d7e214d89070f00ce5e5f,types:40 HQ--------------------------------------------------------------
想抽取的结果是
id qty* types**
1 0 20GP, 40HQ
2 0
3 5 20GP, 40HQ*qty: 字段中所有的qty相加 (不确定有几个qty)
**types: 字段中所有types值串连 (同不确定有几个types)
解决方案 »
- 数据库连接问题~救命啦~急~
- 安装oracle数据库软件时,遇到的与数据库监听程序端口有关的问题。
- 当参数数null的时候,to_date和to_char的时候会报错误
- SQL语句中函数执行次数能否减少?
- 100分!!oracle 9i,win2000 pro,现在想把一台电脑上的一个数据库全部复制到另一台电脑
- 日期的格式问题(在线等待)
- 函数中的游标。
- 如何限制select语句返回的记录数
- oracle连接数目问题
- 请问一个在INSERT语句中嵌套UPPER函数的问题--在线等候,急用!!!
- 用浏览器连接orecal问题
- 使用mybatis框架进行动态编写sql插入语句时报错--违反主键唯一约束
第二个好像要用正则
(
SELECT 1 AS id , '_id:$oid:573e5bb44d89070f00cf65e9,types:20 GP,_id:$oid:573e5bb44d89070f00cf65e8,types:40 HQ' AS value FROM DUAL UNION ALL
SELECT 2 , '_id:$oid:5758a988c74d490f0093a2bc,types:' FROM DUAL UNION ALL
SELECT 3 , 'qty:2,_id:$oid:573d7e214d89070f00ce5e60,types:20 GP,qty:3,_id:$oid:573d7e214d89070f00ce5e5f,types:40 HQ' FROM DUAL)
SELECT ID, SUM(NVL("qty*", 0)) AS "qty*",
LISTAGG("types**", ',') WITHIN GROUP(ORDER BY ID)
FROM (SELECT T.*,
REPLACE(REGEXP_SUBSTR(REGEXP_SUBSTR(T.VALUE, '[^,]+', 1,
COLUMN_VALUE),
'^types:[^,]+', 1, 1), 'types:') AS "types**",
REPLACE(REGEXP_SUBSTR(REGEXP_SUBSTR(T.VALUE, '[^,]+', 1,
COLUMN_VALUE), '^qty:[^,]+',
1, 1), 'qty:') AS "qty*"
FROM TMP T,
TABLE(CAST(MULTISET
(SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(T.VALUE, ',') + 1) AS
SYS.ODCINUMBERLIST)) T2)
GROUP BY ID
(
SELECT 1 AS id , '_id:$oid:573e5bb44d89070f00cf65e9,types:20 GP,_id:$oid:573e5bb44d89070f00cf65e8,types:40 HQ' AS value FROM DUAL UNION ALL
SELECT 2 , '_id:$oid:5758a988c74d490f0093a2bc,types:' FROM DUAL UNION ALL
SELECT 3 , 'qty:2,_id:$oid:573d7e214d89070f00ce5e60,types:20 GP,qty:3,_id:$oid:573d7e214d89070f00ce5e5f,types:40 HQ' FROM DUAL)
select t.*,
regexp_replace(value,'[^,]*qty:([0-9]*,?)|[^,]*,?','\1') a,
regexp_replace(value,'[^,]*types:([^,]*,?)|[^,]*,?','\1') b from tmp tqty 这个你在外层自己写一个自定义的函数,求一下各就可以了