求一Oracle语句
表A
ID CSZ
1 晴
2 雨
3 阴
4 雪
表B
ID TQ
1 1,2
2 2,4
.....
想用一条语句显示结果为:
ID TQ
1 晴,雨
2 雨,雪
.....
注:表A,B是现成已有的表.表B里TQ的值不确定,随意搭配的,有可能是1,2.或是1,2,3,4
[解决办法]
比较复杂一点，用substr把那个列拆分成多个列，然后求出晴，雨什么的，再拼起来。
[解决办法]
路过
[解决办法]
create table strTest( id int , str varchar2(10) );
insert into strTest
select '1','1,2' from dual
union
select '2', '1,3' from dual
union
select '3','1,3,4' from dual
union
select '4','1,3,2,4' from dual;
select decode(substr(str,0,1),'1','晴,','2','雨,','3','阴,','4','雪')||
decode(substr(str,3,1),'1','晴,','2','雨,','3','阴,','4','雪') ||
decode(substr(str,5,1),'1','晴,','2','雨,','3','阴,','4','雪') ||
decode(substr(str,7,1),'1','晴,','2','雨,','3','阴,','4','雪') as TQ
from strTest
