select * from (select a.* from table1 a ,table2 b where a.a=b.a and a.b=b.b and a.c= INPUT) a , (select a.* from table2 a ,table3 b where a.c= INPUT and a.a=b.a and a.b=b.b ) b where a.a=b.a and a.b=b.b;
TO LGQDUCKY(飘):第一个嵌套的select中的table2应该是table3吧,但是这样会出现冗余的数据,比如table1和table2都有的数据。而且,table2中的数据可能比TABLE1的数据要多,如果table1中没数据,这是没问题的,但如果table1中有数据,就不行了。
TO zjhclf(寂寞撩人) :一个sql语句应该不能实现if..else的逻辑,那多个sql语句能实现吗?或者说,sql语句里能嵌套if..else的逻辑吗?请具体说明,或提供相应的文章地址。
select a.* from table1 a ,table3 b where a.a=b.a and a.b=b.b and a.c= INPUT union select a.* from table2 a ,table3 b where a.a=b.a and a.b=b.b and a.c= INPUT
问题已解决: select decode(table1.A,null,table2.A,table1.A) as A, decode(table1.A,null,table2.B,table1.B) as B, decode(table1.A,null,table2.C,table1.C) as C, decode(table1.A,null,table2.D,table1.D) as D from table1,table2,table3, ( select A, B, decode(table1.A,NULL,'table2','table1') as flag from table1,table3 where table3.A = table1.A(+) and table3.B = table1.B(+) ) tempTable where table1.A(+) = table3.A and table1.B(+) = table3.B and table2.A(+) = table3.A and table2.B(+) = table3.B and tempTable.A(+) = table3.A and tempTable.B(+) = table3.B and ( (tempTable.flag = 'table1' and table1.C = INPUT) or (tempTable.flag = 'table2' and table2.C = INPUT) ) 其中A、B为主KEY,NOT NULL。 谢谢大家
不好意思,漏了一些 select decode(table1.A,null,table2.A,table1.A) as A, decode(table1.A,null,table2.B,table1.B) as B, decode(table1.A,null,table2.C,table1.C) as C, decode(table1.A,null,table2.D,table1.D) as D from table1,table2,table3, ( select table3.A as A, table3.B as B, decode(table1.A,NULL,'table2','table1') as flag from table1,table3 where table3.A = table1.A(+) and table3.B = table1.B(+) ) tempTable where table1.A(+) = table3.A and table1.B(+) = table3.B and table2.A(+) = table3.A and table2.B(+) = table3.B and tempTable.A(+) = table3.A and tempTable.B(+) = table3.B and ( (tempTable.flag = 'table1' and table1.C = INPUT) or (tempTable.flag = 'table2' and table2.C = INPUT) )
select a.* from table1 a ,table3 b where a.a=b.a and a.b=b.b and a.c= INPUT union select c.* from table2 c ,table3 d where c.a=d.a and c.b=d.b and c.c= INPUT and not exists ( select 1 from table1 e where c.a=e.a and c.b=e.b )
a.c= INPUT) a ,
(select a.* from table2 a ,table3 b where a.c= INPUT and a.a=b.a and a.b=b.b ) b
where a.a=b.a and a.b=b.b;
union
select a.* from table2 a ,table3 b where a.a=b.a and a.b=b.b and a.c= INPUT
select
decode(table1.A,null,table2.A,table1.A) as A,
decode(table1.A,null,table2.B,table1.B) as B,
decode(table1.A,null,table2.C,table1.C) as C,
decode(table1.A,null,table2.D,table1.D) as D
from table1,table2,table3,
( select A,
B,
decode(table1.A,NULL,'table2','table1') as flag
from table1,table3
where table3.A = table1.A(+)
and table3.B = table1.B(+)
) tempTable
where table1.A(+) = table3.A
and table1.B(+) = table3.B
and table2.A(+) = table3.A
and table2.B(+) = table3.B
and tempTable.A(+) = table3.A
and tempTable.B(+) = table3.B
and (
(tempTable.flag = 'table1' and table1.C = INPUT)
or
(tempTable.flag = 'table2' and table2.C = INPUT)
)
其中A、B为主KEY,NOT NULL。
谢谢大家
select
decode(table1.A,null,table2.A,table1.A) as A,
decode(table1.A,null,table2.B,table1.B) as B,
decode(table1.A,null,table2.C,table1.C) as C,
decode(table1.A,null,table2.D,table1.D) as D
from table1,table2,table3,
( select table3.A as A,
table3.B as B,
decode(table1.A,NULL,'table2','table1') as flag
from table1,table3
where table3.A = table1.A(+)
and table3.B = table1.B(+)
) tempTable
where table1.A(+) = table3.A
and table1.B(+) = table3.B
and table2.A(+) = table3.A
and table2.B(+) = table3.B
and tempTable.A(+) = table3.A
and tempTable.B(+) = table3.B
and (
(tempTable.flag = 'table1' and table1.C = INPUT)
or
(tempTable.flag = 'table2' and table2.C = INPUT)
)
union
select c.* from table2 c ,table3 d where c.a=d.a and c.b=d.b and c.c= INPUT
and not exists ( select 1 from table1 e where c.a=e.a and c.b=e.b )