表AAA
列 A B C
11 11 11
22 22 33
表BBB
列 A B C
11 11 11
22 11 22
33 33 33
希望取出的值为 11 11 11
22 22 22
33 33 33我用的下面语句select A,B,C from AAA
union
select A,B,C from BBB where A not in (select A from AAA)已经实现效果,可是实际中BBB表25万数据...表AAA没看到..比BBB大很多..
并且实际查询时候
select A,B,C from AAA where A='xxx' and B <='xxx' and C>='xxx'
union
select A,B,C from BBB where A='xxx' and B <='xxx' and C>='xxx' A not in (select A from AAA)光执行select A,B,C from BBB where A='xxx' and B <='xxx' and C>='xxx' A not in (select A from AAA)时候等了2分钟,服务器也没返回东西回来..请教比较好的方法...除了说创建新view哈...没权限的..只能查询...
列 A B C
11 11 11
22 22 33
表BBB
列 A B C
11 11 11
22 11 22
33 33 33
希望取出的值为 11 11 11
22 22 22
33 33 33我用的下面语句select A,B,C from AAA
union
select A,B,C from BBB where A not in (select A from AAA)已经实现效果,可是实际中BBB表25万数据...表AAA没看到..比BBB大很多..
并且实际查询时候
select A,B,C from AAA where A='xxx' and B <='xxx' and C>='xxx'
union
select A,B,C from BBB where A='xxx' and B <='xxx' and C>='xxx' A not in (select A from AAA)光执行select A,B,C from BBB where A='xxx' and B <='xxx' and C>='xxx' A not in (select A from AAA)时候等了2分钟,服务器也没返回东西回来..请教比较好的方法...除了说创建新view哈...没权限的..只能查询...
列 A B C
11 11 11
22 22 33
表BBB
列 A B C
11 11 11
22 11 22
33 33 33
希望取出的值为 11 11 11
22 22 22
33 33 33
那个结果 22 22 22
是怎么得出来的?
这个可以不要,
应为union自动会取消重复的行,union all才会有重复的行
列 A B C
11 11 11
22 22 22
表BBB
列 A B C
11 11 11
22 11 22
33 33 33 3楼的,就是AAA表中有A列的B表中就不要了,就是说11 11 11和22,22 22用表AAA,只要表BBB的33 33 33加入进来...而像表BBB中22 11 22和表AAA中22 22 22不一样.就会被选择进来,而这不是我本意...偶的语句已经实现...小范围....当表BBB有25万行数据时候,怎么可以加速?
估计是select A,B,C from BBB B,AAA A WHERE B.A=A.A(+)
红色部分有点问题..感觉...两个表都有A,B,C列...select A,B,C from BBB B,AAA A WHERE B.A=A.A(+) and A.B is null
ORA-00904: 无效列名
union
select B.A, B.B, B.C from BBB B where A='xxx' and B <='xxx' and C> ='xxx' and not exists(select A from AAA C WHERE B.A = C.A)
union
select A,B,C from BBB T1,AAA T2 WHERE T1.A<>T2.A另:
T1.A,T2.A 建下索引,看是否用上?