表a,b做连接查询,behave字段表a独有,要连接查询时,排除behave值为1,3的记录
我写了以下语句
select id from table a
join b
on a.id=b.oid
where a.behave not in (1,3)
and a.time<b.time
但结果出来,还有存在behave为1,3的id ,如果换成in ,也会存在不是1,3的id记录
我找不出原因,请各位指教,谢谢
我写了以下语句
select id from table a
join b
on a.id=b.oid
where a.behave not in (1,3)
and a.time<b.time
但结果出来,还有存在behave为1,3的id ,如果换成in ,也会存在不是1,3的id记录
我找不出原因,请各位指教,谢谢
id int,
behave int,
[time] datetime
)
create table b(
oid int,
[time] datetime
)insert into a
select 1,2,'2007-4-1' union all
select 2,1,'2007-5-4' union all
select 3,3,'2007-6-5' union all
select 4,4,'2007-6-25'insert into b
select 1,'2007-7-1' union all
select 2,'2007-7-2' union all
select 4,'2007-7-3'
select id from a
join b
on a.id=b.oid
where a.behave not in (1,3)
and a.time<b.time
drop table a
drop table b