列转行,再行转列,B表访问次数少了 但是至于那个执行时间快,不确定,具体执行下或看一下执行计划吧 SELECT MAX(DECODE(A.CONAME,'code1',B.codeName)), MAX(DECODE(A.CONAME,'code2',B.codeName)), MAX(DECODE(A.CONAME,'code3',B.codeName)) FROM (select code1 as code,'code1' as colname,rowid as id from TABLEA union all select code2 as code,'code2' as colname,rowid as id from TABLEA union all select code3 as code,'code3' as colname,rowid as id from TABLEA) A,TABLEB B WHERE A.CODE=B.CODE GROUP BY A.ID
select (case when a.code1=b1.code then b1.codename end), (case when a.code1=b1.code then b1.codename end), (case when a.code1=b1.code then b1.codename end) from tabA a left join tabB b1 on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
此方法不可取 取出的结果会一条变三条,如 C1NAME NULL NULL NULL C2NAME NULL NULL NULL C3NAME select (case when a.code1=b1.code then b1.codename end), (case when a.code2=b1.code then b1.codename end), (case when a.code3=b1.code then b1.codename end) from tabA a left join tabB b1 on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
此方法不可取 取出的结果会一条变三条,如 C1NAME NULL NULL NULL C2NAME NULL NULL NULL C3NAME select (case when a.code1=b1.code then b1.codename end), (case when a.code2=b1.code then b1.codename end), (case when a.code3=b1.code then b1.codename end) from tabA a left join tabB b1 on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code) select max(case when a.code1=b.code then b.CODNAME end), max(case when a.code2=b.code then b.CODNAME end), max(case when a.code3=b.code then b.CODNAME end) from a left join b on a.code1=b.code or a.code2=b.code or a.code3= b.code group by a.rowid
--如果满足要求的话 楼主看看这个 SELECT code1, code2, code3, wm_concat(t2.name) FROM t t1 LEFT JOIN tb t2 ON (t1.code1 = t2.code OR t1.code2 = t2.code OR t1.code3 = t2.code) GROUP BY code1, code2, code3
但是至于那个执行时间快,不确定,具体执行下或看一下执行计划吧
SELECT MAX(DECODE(A.CONAME,'code1',B.codeName)),
MAX(DECODE(A.CONAME,'code2',B.codeName)),
MAX(DECODE(A.CONAME,'code3',B.codeName))
FROM
(select code1 as code,'code1' as colname,rowid as id from TABLEA
union all
select code2 as code,'code2' as colname,rowid as id from TABLEA
union all
select code3 as code,'code3' as colname,rowid as id from TABLEA) A,TABLEB B
WHERE A.CODE=B.CODE
GROUP BY A.ID
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end),
(case when a.code1=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
取出的结果会一条变三条,如
C1NAME NULL NULL
NULL C2NAME NULL
NULL NULL C3NAME
取出的结果会一条变三条,如
C1NAME NULL NULL
NULL C2NAME NULL
NULL NULL C3NAME
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code2=b1.code then b1.codename end),
(case when a.code3=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
楼主原本的语句,如果每次经过where筛选后,数据量不大,在tabB的code上加个索引就差不多了
取出的结果会一条变三条,如
C1NAME NULL NULL
NULL C2NAME NULL
NULL NULL C3NAME
select
(case when a.code1=b1.code then b1.codename end),
(case when a.code2=b1.code then b1.codename end),
(case when a.code3=b1.code then b1.codename end)
from tabA a
left join tabB b1
on (a.code1=b1.code or a.code2=b1.code or a.code3= b1.code)
select max(case when a.code1=b.code then b.CODNAME end),
max(case when a.code2=b.code then b.CODNAME end),
max(case when a.code3=b.code then b.CODNAME end) from a left join b on a.code1=b.code or a.code2=b.code or a.code3= b.code
group by a.rowid
--如果满足要求的话 楼主看看这个
SELECT code1, code2, code3, wm_concat(t2.name)
FROM t t1
LEFT JOIN tb t2
ON (t1.code1 = t2.code OR t1.code2 = t2.code OR t1.code3 = t2.code)
GROUP BY code1, code2, code3