执行上面的两条语句当然会得到不相同的结果,因为只要A表或B表中存在一条满足exists这个条件的记录,那么对整个查询来说这个exists条件就永远为TRUE,也就是个多余的条件。因此第一条语句就会只查满足 “and original_file like 'N%' and start_time > to_date ( '20041008','yyyymmdd')” 这个条件的记录,而第二条语句则只查满足“and original_file like 'N%'”这个条件的记录。当然查出的结果也会包含满足exists这个条件的记录。
你的原因在于like 例如 A:ID NAME 1 A 2 B 3 C B: ID NAME 1 A 1 AA 2 B
SQL> SELECT * FROM A WHERE EXISTS (SELECT ID FROM B WHERE A.ID=B.ID) 2 AND A.NAME LIKE 'A%' 3 ;ID NAME --- ------------------------------ 1 ASQL> SELECT * FROM B WHERE EXISTS (SELECT ID FROM A WHERE A.ID=B.ID AND A.NAME LIKE'A%');ID NAME --- -------------------- 1 A 1 AA
bluelamb(bluelamb) ,呵呵,厉害
我用的数据库是oracle 9i for hpux语句一 select count(*) from a where exists ( select * from b where a.user_number = b.user_number and a.sequence_no = b.sequence_no and a.start_time = b.start_time and a.call_type = b.call_type)
语句二 select count(*) from b where exists ( select * from a where a.user_number = b.user_number and b.sequence_no = a.sequence_no and b.start_time = a.start_time and a.call_type = b.call_type)我现在用这样的语句执行,仍然得到不同的结果 user_number,sequence_no,start_time,call_type组成一个唯一索引 这四个字段能保证数据的唯一性 a表有70万条记录,b表有90万条记录语句一的结果是68万条,而语句二的结果是90万条,跟B表的记录数一样语句三 select count(*) from (select user_number,sequence_no,start_time,call_type from a intersect select user_number,sequence_no,start_time,call_type from b) 我用语句三验证表中的相同的记录数是68万条,说明语句一执行的结果是正确的
这四个字段能保证数据的唯一性
如果user_number,sequence_no,start_time,call_type这四个字段能保证数据的唯一性的话,两个查询的结果应该是一样的。
期待高手解决!
例如
A:ID NAME
1 A
2 B
3 C
B: ID NAME
1 A
1 AA
2 B
2 AND A.NAME LIKE 'A%'
3 ;ID NAME
--- ------------------------------
1 ASQL> SELECT * FROM B WHERE EXISTS (SELECT ID FROM A WHERE A.ID=B.ID AND A.NAME LIKE'A%');ID NAME
--- --------------------
1 A
1 AA
select count(*) from a where exists ( select * from b
where a.user_number = b.user_number
and a.sequence_no = b.sequence_no
and a.start_time = b.start_time
and a.call_type = b.call_type)
语句二
select count(*) from b where exists ( select * from a
where a.user_number = b.user_number
and b.sequence_no = a.sequence_no
and b.start_time = a.start_time
and a.call_type = b.call_type)我现在用这样的语句执行,仍然得到不同的结果
user_number,sequence_no,start_time,call_type组成一个唯一索引
这四个字段能保证数据的唯一性
a表有70万条记录,b表有90万条记录语句一的结果是68万条,而语句二的结果是90万条,跟B表的记录数一样语句三
select count(*) from
(select user_number,sequence_no,start_time,call_type from a
intersect
select user_number,sequence_no,start_time,call_type from b)
我用语句三验证表中的相同的记录数是68万条,说明语句一执行的结果是正确的