简单一点说表table1 有字段type 和code
其中type中数据为a1-a10的表名 a1-a10表中都包含name和code字段
假如table1表中有一条数据为
type code
a5 100a5表中有一条数据为
name code
aaa 100select语句应该是
select t2.name,t1.code from (select type,code from table1 where type = 'a1') t1 left join a1 t2 on t1.code = t2.code
union
select ...... from (........ type = 'a2') t1
left join a2 .......
union
...
...
union
select t2.name,t1.code from (select type,code from table1 where type = 'a10') t1 left join a10 t2 on t1.code = t2.code有没有办法简化一下啊??
因为现在假设是10个表
如果是100个哪??不是要union 99次吗???
其中type中数据为a1-a10的表名 a1-a10表中都包含name和code字段
假如table1表中有一条数据为
type code
a5 100a5表中有一条数据为
name code
aaa 100select语句应该是
select t2.name,t1.code from (select type,code from table1 where type = 'a1') t1 left join a1 t2 on t1.code = t2.code
union
select ...... from (........ type = 'a2') t1
left join a2 .......
union
...
...
union
select t2.name,t1.code from (select type,code from table1 where type = 'a10') t1 left join a10 t2 on t1.code = t2.code有没有办法简化一下啊??
因为现在假设是10个表
如果是100个哪??不是要union 99次吗???
1,
A1.AName,
2,
A2.AName,
....,
10,
A10.AName),
t.ACode from t, A1, A2, A3,.. .A10
where t.ACode = A1.ACode(+)
and t.ACode = A2.ACode(+)
and t.ACode = A3.ACode(+)
....
and t.ACode = A10.ACode(+)
UNION ALLselect decode(to_number(t.BCode),
1,
B1.BName,
2,
B2.BName,
....,
10,
B10.BName),
t.BCode from t, B1, B2, B3,.. .B10
where t.BCode = B1.ACode(+)
and t.BCode = B2.ACode(+)
and t.BCode = B3.ACode(+)
....
and t.BCode = B10.ACode(+)
select decode(to_number(t.ACode),...
....
select decode(to_number(t.BCode),...
-------------------->修正:
select decode(to_number(t.Atype),...
....
select decode(to_number(t.Btype),...
select decode(to_number(t.Atype),
1,
A1.AName,
2,
A2.AName,
....,
10,
A10.AName),
t.ACode,
decode(to_number(t.Btype),
1,
B1.BName,
2,
B2.BName,
....,
10,
B10.BName),
t.BCode from t, A1, A2, A3,.. .A10
, B1, B2, B3,.. .B10
where t.ACode = A1.ACode(+)
and t.ACode = A2.ACode(+)
and t.ACode = A3.ACode(+)
....
and t.ACode = A10.ACode(+)
and t.BCode = B1.BCode(+)
and t.BCode = B2.BCode(+)
and t.BCode = B3.BCode(+)
....
and t.BCode = B10.BCode(+)