SELECT field1 FROM A
WHERE Key1 ='999999999999999'
AND ( SELECT field1 FROM A
WHERE Key1 ='999999999999999' )
IN ( SELECT field2 FROM B WHERE field1 = '4') SELECT A.field1 FROM A,B
WHERE Key1 ='999999999999999'
AND A.field1 = B.field2 and B.field1 = '4'---------------------------------------
感觉1写的太复杂了,如何证明2等价1。
有优化工具可以自动把1变为2吗?
WHERE Key1 ='999999999999999'
AND ( SELECT field1 FROM A
WHERE Key1 ='999999999999999' )
IN ( SELECT field2 FROM B WHERE field1 = '4') SELECT A.field1 FROM A,B
WHERE Key1 ='999999999999999'
AND A.field1 = B.field2 and B.field1 = '4'---------------------------------------
感觉1写的太复杂了,如何证明2等价1。
有优化工具可以自动把1变为2吗?
有优化工具可以自动把1变为2吗?
http://community.csdn.net/Expert/topic/4602/4602357.xml?temp=.4718592
如果表A中Key1 ='999999999999999'的记录多于1行,则语句1是会出错的;如果表A与表B不是一一对应,而是1对多,那么语句2返回的记录会比1多。
简化试试:
SELECT field1 FROM A
WHERE Key1 ='999999999999999'
AND field1 IN ( SELECT field2 FROM B WHERE field1 = '4')
WHERE Key1 ='999999999999999'
AND A.field1 = B.field2 and B.field1 = '4'=SELECT field1 FROM A
WHERE Key1 ='999999999999999'
AND field1 IN ( SELECT field2 FROM B WHERE field1 = '4')=SELECT field1 FROM A
WHERE Key1 ='999999999999999'
AND ( SELECT field1 FROM A
WHERE Key1 ='999999999999999' )
IN ( SELECT field2 FROM B WHERE field1 = '4')
----------------------------------------------------------------------------------to all:
我做数据试了,相同。
delete B;
insert into A(key1, field1) values('999999999999999',1);
insert into B(field1, field2,field3) values('4',1,1);
insert into B(field1, field2,field3) values('4',1,2);
commit;
用这个数据试试
insert into A(key1, field1) values('999999999999999',2);
再用这个数据试试