17,377
社区成员
发帖
与我相关
我的任务
分享
Select ENFIELD,CHNAME,VALUE From t1 a INNER JOIN
(
Select 'A' as ID,(select RTrim(A) from t2 ) as VALUE FROM t2 --此处可以增加where条件
union all
Select 'B' as ID,(select RTrim(B) from t2 ) as VALUE FROM t2
union all
Select 'C' as ID,(select RTrim(C) from t2 ) as VALUE FROM t2
--如果有,继续拼接
......
) b
on a.ENFIELD=b.ID
and ((ENFIELD='A'and VALUE='值1') or (ENFIELD='C'and VALUE= '值3')) --过滤条件。
SQL> select * from table1;
ENFIELD CHNAME
------- ----------
A id1
B id2
C id3
D id4
E id5
SQL> select * from table2;
A B C D E
---------- ---------- ---------- ---------- ----------
value1 value2 value3 value4 value5
SQL>
SQL> select t1.ENFIELD, t1.CHNAME, tt2.cols_values
2 from table1 t1,
3 (select substr(',' || cols || ',',
4 instr(',' || cols || ',', ',', 1, 2 * rn - 1) + 1,
5 instr(',' || cols || ',', ',', 1, 2 * rn - 1 + 1) -
6 instr(',' || cols || ',', ',', 1, 2 * rn - 1) - 1) as new_cols,
7 substr(',' || cols || ',',
8 instr(',' || cols || ',', ',', 1, 2 * rn) + 1,
9 instr(',' || cols || ',', ',', 1, 2 * rn + 1) -
10 instr(',' || cols || ',', ',', 1, 2 * rn) - 1) as cols_values
11 from (select rownum rn from all_objects where rownum <= 21) ao,
12 (select 'A' || ',' || A || ',' || 'B' || ',' || B || ',' || 'C' || ',' || C || ',' || 'D' || ',' || D || ',' || 'E' || ',' || E as cols
13 from table2) t2
14 where instr(',' || cols, ',', 1, 2 * rn - 1) > 0) tt2
15 where t1.ENFIELD = tt2.new_cols;
ENFIELD CHNAME COLS_VALUES
------- ---------- ------------------------------------------------------------------
A id1 value1
B id2 value2
C id3 value3
D id4 value4
E id5 value5
SQL>
DECLARE
colNum INT DEFAULT 5;
str VARCHAR2(2000);
BEGIN
FOR i IN 1..colnum LOOP
IF length(str)>0 THEN
str:=str||','''||chr(64+i)||''','||chr(64+i);
ELSE
str:=''''||chr(64+i)||''','||chr(64+i);
END IF;
END LOOP;
str:='decode(ENFIELD,'||str||')';
dbms_output.put_line(str);
END;
然后利用上面生成的decode字符串,代替下面的decode字符串,就可以了。
SELECT ENFIELD,
CHNAME,
DECODE(ENFIELD, 'A', A, 'B', B, 'C', C, 'D', D, 'E', E) vv
FROM TA, TB;
如果第二个表有一行数据的话,也可以:
SELECT ENFIELD,
CHNAME,
DECODE(ENFIELD, 'A', A, 'B', B, 'C', C, 'D', D, 'E', E)
FROM TA, TB;
输出结果:
A 序号1 值1
B 序号2 值2
C 序号3 值3
D 序号4 值4
E 序号5 值5
如果单纯是一对一功能,可以这么实现啊:
SELECT ENFIELD,
CHNAME,
DECODE(ENFIELD,
'A',
'值1',
'B',
'值2',
'C',
'值3',
'D',
'值4',
'E',
'值5')
FROM TA;
输出结果:
A 序号1 值1
B 序号2 值2
C 序号3 值3
D 序号4 值4
E 序号5 值5
[Quote=引用楼主 jrq 的帖子:]