表PRODUCT 有下面的数据, 数据是以“,”分开的Cn,GD
TW,GD
CN,FJ
CN,HN关联refer表查找cn,gd,tw对应的值,select desc from refer where code='cn'
表refer是以下数据
code desc
CN->CHINA
TW->TAIWAN
GD->GUANGDONG
FJ->FUJIAN
怎么通过上面的条件查出下面这个数据?
CHINA,GUANGDONG
TAIWAN,GUANGDONG
CHINA,FUJIAN
CHINA,HUNAN
TW,GD
CN,FJ
CN,HN关联refer表查找cn,gd,tw对应的值,select desc from refer where code='cn'
表refer是以下数据
code desc
CN->CHINA
TW->TAIWAN
GD->GUANGDONG
FJ->FUJIAN
怎么通过上面的条件查出下面这个数据?
CHINA,GUANGDONG
TAIWAN,GUANGDONG
CHINA,FUJIAN
CHINA,HUNAN
解决方案 »
- 求时间间隔的日期
- 谁能帮我看看这个正则表达式的意思 regexp_substr(sql_text, '/\*.*_\d{8}\_\d{6,}')
- 关于to_char的两个问题
- oracle 安装目录移动导致的问题
- 我要用这个函数,UTL_FILE.PUT_LINE(); 来向文件里写数据,那预先在sql*plus里面打什么命令设置一下,才行呢??
- ★☆★☆★☆★实现多台服务器之间数据库表的同步更新问题,急!
- 将多个表a1,a2...(结构一样)的某些字段的数据导到一个表b(b的结构包含a需要导的字段,另外多出字段a没有的,想在导的时候插进去个变量)里面
- 在SQL*PLUS中insert into 的问题
- 在ORACLE中如何访问其他用户的表或者视图???
- oracle 安装问题!急
- sqlplus 可以登录但是pl/sql 却不行
- 一个Update语句求教
TW,GD
CN,FJ
CN,HN select desc from refer where code='cn' 返回下面数据CHINA
之前有帖子发过,参考
17:17:15 scott@TUNGKONG> select * from table1;ID NUMS
-------------------- ----------
300:301:302 1
300:302 1
300:301:400 1
302:303:400 1
301:303 1
400 3已选择6行。已用时间: 00: 00: 00.04
17:17:22 scott@TUNGKONG> select * from table2;ID NAME
-------------------- --------------------
300 AAAA
301 BBBB
302 CCCC
303 DDDD
400 EEEE已用时间: 00: 00: 00.00
17:17:24 scott@TUNGKONG> CREATE OR REPLACE FUNCTION fun_test(tmp varchar2)
17:17:30 2 RETURN varchar2
17:17:30 3 IS
17:17:30 4 rlt varchar2(100);
17:17:30 5 CURSOR myCursor IS SELECT id,name FROM table2;
17:17:30 6 BEGIN
17:17:30 7 rlt := tmp;
17:17:30 8 FOR cur IN myCursor LOOP
17:17:30 9 rlt := REPLACE(rlt,cur.id,cur.name);
17:17:30 10 END LOOP;
17:17:30 11 RETURN rlt;
17:17:30 12 END;
17:17:30 13 /函数已创建。已用时间: 00: 00: 00.01
17:17:31 scott@TUNGKONG> select fun_test(t.id)as NAME,t.nums from table1 t;NAME NUMS
-------------------- ----------
AAAA:BBBB:CCCC 1
AAAA:CCCC 1
AAAA:BBBB:EEEE 1
CCCC:DDDD:EEEE 1
BBBB:DDDD 1
EEEE 3已选择6行。已用时间: 00: 00: 00.00
select 'CN,GD' code from dual union all
select 'TW,GD' code from dual union all
select 'CN,FJ' code from dual union all
select 'CN,HN' code from dual
),
refer as
(
select 'CN' code,'CHINA' desc_ from dual union all
select 'TW' code,'TAIWAN' desc_ from dual union all
select 'GD' code,'GUANGDONG' desc_ from dual union all
select 'FJ' code,'FUJIAN' desc_ from dual union all
select 'HN' code,'HUNAN' desc_ from dual
)
select rn, wm_concat(r.desc_)
from (select regexp_replace(code, '(.*),(.*)', '\1') code,
row_number() over(order by 1) rn
from product
union all
select regexp_replace(code, '(.*),(.*)', '\2') code,
row_number() over(order by 1) rn
from product) p,
refer r
where r.code = p.code
group by rn
SQL>
SQL> with PRODUCT as (
2 select 'CN,GD' code from dual union all
3 select 'TW,GD' code from dual union all
4 select 'CN,FJ' code from dual union all
5 select 'CN,HN' code from dual
6 ),
7 refer as
8 (
9 select 'CN' code,'CHINA' desc_ from dual union all
10 select 'TW' code,'TAIWAN' desc_ from dual union all
11 select 'GD' code,'GUANGDONG' desc_ from dual union all
12 select 'FJ' code,'FUJIAN' desc_ from dual union all
13 select 'HN' code,'HUNAN' desc_ from dual
14 )
15 select rn, wm_concat(r.desc_)
16 from (select regexp_replace(code, '(.*),(.*)', '\1') code,
17 row_number() over(order by 1) rn
18 from product
19 union all
20 select regexp_replace(code, '(.*),(.*)', '\2') code,
21 row_number() over(order by 1) rn
22 from product) p,
23 refer r
24 where r.code = p.code
25 group by rn
26 / RN WM_CONCAT(R.DESC_)
---------- --------------------------------------------------------------------------------
1 CHINA,GUANGDONG
2 TAIWAN,GUANGDONG
3 CHINA,FUJIAN
4 CHINA,HUNANSQL>
select 1 id,'CN,GD' code from dual
union all select 2,'TW,GD' from dual
union all select 3,'CN,FJ' from dual
union all select 4,'CN,HN' from dual),
refer as(
select 'CN' code,'CHINA' "desc" from dual
union all select 'TW','TAIWAN' from dual
union all select 'GD','GUANGDONG' from dual
union all select 'FJ','FUJIAN' from dual)select id, code, substr(max(sys_connect_by_path("desc", ',')), 2) newcode
from (select a.id,
a.code,
b."desc",
row_number() over(partition by a.id order by instr(',' || a.code || ',', ',' || b.code || ',')) rn
from product a, refer b
where instr(',' || a.code || ',', ',' || b.code || ',') > 0)
start with rn = 1
connect by prior id = id
and rn = prior rn + 1
group by id, code
order by id;
假设product两个字段分别对应字段名descselect b.desc,c.desc
from PRODUCT a,
refer b,
refer c
where substr(a.desc,1,strstr(a.desc,',') - 1)=b.code and
substr(a.desc,strstr(a.desc,',') + 1,length(a.desc) -strstr(a.desc,',') )=c.code
select 1 id,'CN,GD' code from dual
union all select 2,'TW,GD' from dual
union all select 3,'CN,FJ' from dual
union all select 4,'CN,HN' from dual),
refer as(
select 'CN' code,'CHINA' "desc" from dual
union all select 'TW','TAIWAN' from dual
union all select 'GD','GUANGDONG' from dual
union all select 'FJ','FUJIAN' from dual
union all select 'HN','HUNAN' from dual)select id, code, substr(max(sys_connect_by_path("desc", ',')), 2) newcode
from (select a.id,
a.code,
b."desc",
row_number() over(partition by a.id order by instr(',' || a.code || ',', ',' || b.code || ',')) rn
from product a, refer b
where instr(',' || a.code || ',', ',' || b.code || ',') > 0)
start with rn = 1
connect by prior id = id
and rn = prior rn + 1
group by id, code
order by id;