in 子查询中的数据正确，再套一层结果出错

Question

select id from test 
where id in (SELECT test.ID FROM dbo.Test left JOIN
dbo.RECEIVED ON REPLACE(dbo.RECEIVED.Message, ' ', '') = dbo.Test.Message AND dbo.RECEIVED.Mobile = dbo.Test.Mobile
WHERE (dbo.RECEIVED.ReceiveTime > '2009-12-18 0:00:00') AND (dbo.RECEIVED.ReceiveTime < '2009-12-18 23:40:00'))
这是sql语句，非常奇怪！整个表一共198行数据，位于in 后面的子查询结果是144行，但是再在外边套上select id from test where id in就把所有的给查出来了。
大家谁遇到过这样的问题吗？谢谢

Answer 1

198 - 144 = 54估计有重复的ID.select id from test group by id having count(1) > 1看看有没有重复的ID.

Answer 2

把你的结果直接贴出来看看，select id from test 
where id in (
SELECT test.ID 
FROM dbo.Test left JOIN dbo.RECEIVED 
ON REPLACE(dbo.RECEIVED.Message, ' ', '') = dbo.Test.Message 
AND dbo.RECEIVED.Mobile = dbo.Test.Mobile
WHERE (dbo.RECEIVED.ReceiveTime > '2009-12-18 0:00:00') AND (dbo.RECEIVED.ReceiveTime  < '2009-12-18 23:40:00')
)的结果和 (198 条) SELECT test.ID 
FROM dbo.Test left JOIN dbo.RECEIVED 
ON REPLACE(dbo.RECEIVED.Message, ' ', '') = dbo.Test.Message 
AND dbo.RECEIVED.Mobile = dbo.Test.Mobile
WHERE (dbo.RECEIVED.ReceiveTime > '2009-12-18 0:00:00') AND (dbo.RECEIVED.ReceiveTime  < '2009-12-18 23:40:00')结果 (144 条)
贴到ＥＸＣＥＬ中对比一下，哪条不应该出现？

Answer 3

谢谢dawugui的回复，刚才查了一下，里边并没有重复的id。真是郁闷

Answer 4

应该是你的子查询里面的id已经包含了当前表的所有可能的id呢。

Answer 5

如果不存在重复id,那则可能如楼上所说,查看执行计划可以看得出来 ,以前有过类似的问题，解决办法就是强制写别名，不要让语句发生岐义，虽然查询逻辑中有就近原则，但优化器就是个黑箱，谁也说不好。楼主可以试一下下面的写法，看是否也是会多出记录select id from test x
where id in (SELECT test.ID FROM Test a left JOIN 
dbo.RECEIVED b ON REPLACE(b.Message, ' ', '') = a.Message AND b.Mobile = a.Mobile 
WHERE (b.ReceiveTime > '2009-12-18 0:00:00') AND (b.ReceiveTime < '2009-12-18 23:40:00')) 

Answer 6

select id from test
where id in 
(
SELECT test.ID 
FROM dbo.Test LEFT JOIN dbo.RECEIVED 
ON REPLACE(dbo.RECEIVED.Message, ' ', '') = dbo.Test.Message 
AND dbo.RECEIVED.Mobile = dbo.Test.Mobile
WHERE (dbo.RECEIVED.ReceiveTime > '2009-12-18 0:00:00') 
AND (dbo.RECEIVED.ReceiveTime < '2009-12-18 23:40:00')
) 注意是LEFT JOIN , 所以子查询中是将test所有记录都取出来的.

Answer 7

不会，因为有where条件。

Answer 8

where 条件是dbo.RECEIVED 的，即left join 右边的啦。

Answer 9

这个我当然知道了，呵呵。
从逻辑上来说where处理的是连接后的结果集，它与是inner join或left join是没有关系的。 on条件右输入不符时才会左输入原样输出.
如果on时右输入不匹配得到 null, 那么在结果集的where 过滤时当然是会被filter掉了。

Answer 10

create table test(id int,message varchar(10),mobile varchar(11))
create table RECEIVED(id int,message varchar(10),mobile varchar(11),receivetime datetime)insert into test
select 1,'ABC','13900000001' union all
select 2,'ABC','13900000001' union all
select 3,'','13900000003'insert into RECEIVED
select 1,'ABC','13900000001','2009-12-18 0:10:00' union all
select 2,'ABC','13900000001','2009-12-18 0:10:00' union all
select 3,'ABC ','13900000001','2009-12-18 0:10:00'SELECT test.ID
FROM dbo.Test
left JOIN dbo.RECEIVED
ON REPLACE(dbo.RECEIVED.Message, ' ', '') = dbo.Test.Message
AND dbo.RECEIVED.Mobile = dbo.Test.Mobile
WHERE (dbo.RECEIVED.ReceiveTime > '2009-12-18 0:00:00')
AND (dbo.RECEIVED.ReceiveTime < '2009-12-18 23:40:00')SELECT test.ID
FROM dbo.Test
left JOIN dbo.RECEIVED
ON REPLACE(dbo.RECEIVED.Message, ' ', '') = dbo.Test.Message
AND dbo.RECEIVED.Mobile = dbo.Test.Mobile
AND (dbo.RECEIVED.ReceiveTime > '2009-12-18 0:00:00')
AND (dbo.RECEIVED.ReceiveTime < '2009-12-18 23:40:00')
这两个的执行结果是不同的个人认为LEFT JOIN的时候右表条件要在ON后
不要放到WHERE后，不然结果会很奇怪