查看: 14488|回复: 11

[SQL] 【讨论】请教如何实现一条记录中的多次替换

[复制链接]
论坛徽章:
2
ITPUB 11周年纪念徽章
日期:2012-10-09 18:11:48优秀写手
日期:2014-07-15 06:00:13
发表于 2014-7-12 16:16 | 显示全部楼层 |阅读模式
需求是希望能够将对应的key替换成相应的名称:
比如有张key-name映射表,大概1000条记录
a=>长度
b=>国家
c=>重量
.......

目前的数据格式为
a:1,b:2,c:3........
....
.....

最终希望显示如下格式
长度:1,国家:2,重量:3

原始数据中有些记录可以替换一次就ok了,有些记录可能需要替换几十甚至上百,这个以实际记录格式为准,求指导,感谢
求职 : 数据库管理员
招聘 : Java研发
认证徽章
论坛徽章:
6348
ITPUB9周年纪念徽章
日期:2014-05-02 10:36:402011新春纪念徽章
日期:2014-12-29 12:11:142010广州亚运会纪念徽章:卡巴迪
日期:2014-08-06 08:44:252012新春纪念徽章
日期:2014-12-29 12:11:142013年新春福章
日期:2014-12-29 12:11:14马上有车
日期:2014-12-29 12:11:14马上有房
日期:2014-12-29 12:11:14马上有钱
日期:2014-12-29 12:11:14马上有对象
日期:2014-12-29 12:11:14马上加薪
日期:2014-12-29 12:11:14
发表于 2014-7-12 17:12 | 显示全部楼层
  1. WITH T1 AS (select 'a=>长度' a FROM DUAL UNION
  2. select 'b=>国家' a FROM DUAL UNION
  3. select 'c=>重量' a FROM DUAL)
  4. , T2 AS (
  5. select 'a:1' B FROM DUAL UNION
  6. select 'b:2' B FROM DUAL UNION
  7. select 'c:3' B FROM DUAL
  8. )
  9. ,T3 as (
  10. SELECT SUBSTR(a,1,1) co,SUBSTR(a,4,length(a)) nm FROM T1)
  11. , T4 as (
  12. SELECT SUBSTR(b,1,1) co,SUBSTR(b,3,length(b)) nm FROM T2
  13. )
  14. select t3.nm||':'||t4.nm from t3,t4 where t3.co = t4.co
复制代码

使用道具 举报

回复
求职 : 数据库管理员
招聘 : Java研发
认证徽章
论坛徽章:
6348
ITPUB9周年纪念徽章
日期:2014-05-02 10:36:402011新春纪念徽章
日期:2014-12-29 12:11:142010广州亚运会纪念徽章:卡巴迪
日期:2014-08-06 08:44:252012新春纪念徽章
日期:2014-12-29 12:11:142013年新春福章
日期:2014-12-29 12:11:14马上有车
日期:2014-12-29 12:11:14马上有房
日期:2014-12-29 12:11:14马上有钱
日期:2014-12-29 12:11:14马上有对象
日期:2014-12-29 12:11:14马上加薪
日期:2014-12-29 12:11:14
发表于 2014-7-12 17:13 | 显示全部楼层
要是在复杂点就用的上正则表达式了

使用道具 举报

回复
求职 : 数据库管理员
招聘 : Java研发
认证徽章
论坛徽章:
6348
ITPUB9周年纪念徽章
日期:2014-05-02 10:36:402011新春纪念徽章
日期:2014-12-29 12:11:142010广州亚运会纪念徽章:卡巴迪
日期:2014-08-06 08:44:252012新春纪念徽章
日期:2014-12-29 12:11:142013年新春福章
日期:2014-12-29 12:11:14马上有车
日期:2014-12-29 12:11:14马上有房
日期:2014-12-29 12:11:14马上有钱
日期:2014-12-29 12:11:14马上有对象
日期:2014-12-29 12:11:14马上加薪
日期:2014-12-29 12:11:14
发表于 2014-7-12 17:19 | 显示全部楼层
  1. WITH T1 AS (select 'a=>长度' a FROM DUAL UNION
  2. select 'b=>国家' a FROM DUAL UNION
  3. select 'c=>重量' a FROM DUAL)
  4. select a,
  5.        regexp_replace(a, '(.*)=>(.*)', '\1') code,
  6.        regexp_replace(a, '(.*)=>(.*)', '\2') value
  7.   from t1;
复制代码

使用道具 举报

回复
论坛徽章:
403
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2014-7-12 17:40 来自手机 | 显示全部楼层
行转列替换后再转行

使用道具 举报

回复
论坛徽章:
2
ITPUB 11周年纪念徽章
日期:2012-10-09 18:11:48优秀写手
日期:2014-07-15 06:00:13
 楼主| 发表于 2014-7-12 17:49 | 显示全部楼层
2009532140 发表于 2014-7-12 17:12

感谢你的答复,我尝试了你的代码,确实可以实现key-name的转换,但是转换后是变成了多条记录,而我这边每条记录中都有3到10个左右的key-name需要转换,整个数据源加起来的需要转换的key-name得达到100左右,按照你下面的正则替换好像没有达到转换的结果

使用道具 举报

回复
论坛徽章:
2
ITPUB 11周年纪念徽章
日期:2012-10-09 18:11:48优秀写手
日期:2014-07-15 06:00:13
 楼主| 发表于 2014-7-12 19:55 | 显示全部楼层
〇〇 发表于 2014-7-12 17:40
行转列替换后再转行

感谢 行转列和列转行还不太会写,我通过游标把数据给替换了!

使用道具 举报

回复
论坛徽章:
17
2013年新春福章
日期:2013-02-25 14:51:242015年新春福章
日期:2015-03-04 14:53:16马上加薪
日期:2014-12-18 01:09:30祖国65周年纪念徽章
日期:2014-09-27 08:49:22马上有房
日期:2014-07-16 11:59:32优秀写手
日期:2014-07-10 06:00:12马上加薪
日期:2014-04-20 23:04:51马上有对象
日期:2014-04-19 23:32:33马上有车
日期:2014-03-29 23:05:24问答徽章
日期:2014-01-17 15:19:03
发表于 2014-7-13 17:29 | 显示全部楼层
本帖最后由 AnddyHua 于 2014-7-13 17:32 编辑

补充下:

  1. CREATE TABLE key_value_map(ID int,NAME VARCHAR2(32));
  2. DROP TABLE key_value_map;
  3. INSERT INTO key_value_map values(1,'a=>长度');
  4. INSERT INTO key_value_map values(2,'b=>国家');
  5. INSERT INTO key_value_map values(3,'c=>重量');
  6. INSERT INTO key_value_map values(4,'d=>地市');
  7. INSERT INTO key_value_map values(5,'e=>尺寸');
  8. INSERT INTO key_value_map values(6,'f=>平方');
  9. INSERT INTO key_value_map values(7,'h=>维度');
  10. INSERT INTO key_value_map values(8,'g=>身份');
  11. commit;
  12. DROP TABLE key_value_map_result;
  13. CREATE TABLE key_value_map_result AS
  14. select ID,
  15.        regexp_replace(NAME, '(.*)=>(.*)', '\1') str,
  16.        regexp_replace(NAME, '(.*)=>(.*)', '\2') value
  17.   from key_value_map;
  18. CREATE TABLE t_map_data(row_order_id INT,map_data VARCHAR(100));
  19. DROP TABLE map_data;
  20. INSERT INTO t_map_data VALUES(1,'a:1,b:2,c:3,a:1,b:2,c:3,a:1,b:2,c:3');
  21. INSERT INTO t_map_data VALUES(2,'c:1,b:2,c:3,d:1,b:2,f:3,a:1,e:2,c:3');
  22. INSERT INTO t_map_data VALUES(3,'c:1,b:2,c:3,d:1,h:2,f:3,a:1,e:2,c:3');
  23. INSERT INTO t_map_data VALUES(4,'c:1,e:2,f:3,g:1,b:2,f:3,a:1,g:2,f:3');
  24. commit;
  25. CREATE TABLE map_temp_result(map_data_str VARCHAR2(20),map_data_VALUE INT,str_order INT,row_order_id INT);
  26. INSERT INTO  map_temp_result
  27. SELECT SUBSTR(ctx,1,INSTR(ctx,':')-1) str,substr(ctx,INSTR(ctx,':')+1,LENGTH(ctx)) VALUE ,str_order,row_order_id FROM (

  28.     SELECT map_data,
  29.            regexp_substr(map_data,
  30.                          '[^][,]+',
  31.                          1,
  32.                          LEVEL ) AS ctx,LEVEL str_order,row_order_id
  33.     FROM   t_map_data
  34.     CONNECT BY PRIOR dbms_random.value IS NOT NULL
  35.                AND LEVEL <= regexp_count(map_data,
  36.                                          ',')+1
  37.                AND PRIOR map_data = map_data);
  38. SELECT * FROM map_temp_result;

  39. CREATE TABLE map_final_result AS SELECT m.map_data_str map_before_str ,k.value map_after_str ,m.map_data_VALUE,m.str_order,m.row_order_id FROM map_temp_result m,key_value_map_result k
  40. WHERE m.map_data_str(+)=k.str;

  41. SELECT * FROM map_final_result ORDER BY row_order_id,str_order;

  42. SELECT row_order_id,listagg(concat(concat(map_after_str,':'),map_data_value),',') within GROUP (order by str_order)  
  43. from map_final_result
  44. group by  row_order_id;

复制代码
测试:
t_map_datakey_value_map_result
1
a:1,b:2,c:3,a:1,b:2,c:3,a:1,b:2,c:3
2
c:1,b:2,c:3,d:1,b:2,f:3,a:1,e:2,c:3
3
c:1,b:2,c:3,d:1,h:2,f:3,a:1,e:2,c:3
4
c:1,e:2,f:3,g:1,b:2,f:3,a:1,g:2,f:3
  
1
a长度
2
b国家
3
c重量
4
d地市
5
e尺寸
6
f平方
7
h维度
8
g身份
测试结果:
reduce_final_result
1
长度:1,国家:2,重量:3,长度:1,国家:2,重量:3,长度:1,国家:2,重量:3
2
重量:1,国家:2,重量:3,地市:1,国家:2,平方:3,长度:1,尺寸:2,重量:3
3
重量:1,国家:2,重量:3,地市:1,维度:2,平方:3,长度:1,尺寸:2,重量:3
4
重量:1,尺寸:2,平方:3,身份:1,国家:2,平方:3,长度:1,身份:2,平方:3


使用道具 举报

回复
论坛徽章:
403
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2014-7-13 20:54 | 显示全部楼层
AnddyHua 发表于 2014-7-13 17:29
补充下:测试:
[/td][/tr]

不错,但lz这种表结构设计真的难用

使用道具 举报

回复
论坛徽章:
17
2013年新春福章
日期:2013-02-25 14:51:242015年新春福章
日期:2015-03-04 14:53:16马上加薪
日期:2014-12-18 01:09:30祖国65周年纪念徽章
日期:2014-09-27 08:49:22马上有房
日期:2014-07-16 11:59:32优秀写手
日期:2014-07-10 06:00:12马上加薪
日期:2014-04-20 23:04:51马上有对象
日期:2014-04-19 23:32:33马上有车
日期:2014-03-29 23:05:24问答徽章
日期:2014-01-17 15:19:03
发表于 2014-7-13 22:38 | 显示全部楼层
本帖最后由 AnddyHua 于 2014-7-13 22:43 编辑
〇〇 发表于 2014-7-13 20:54
不错,但lz这种表结构设计真的难用

感觉如果那个mapping关系其他地方用不到或者长期不变就处理而言,直接像下面牺牲下3范那样不是更好
str_order和row_order_id做主键
搜狗截图20140713223315.png
或者3范,那样map_before_str要变成外键

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表