如何找出表2有而表1没有的记录
各位朋友,表1和表2如下:
语句:select * from 表2 where no not in(select no from 表1) 可以找出表2有而表1没有的记录,但是,想加多一个条件,找出来的结果,最大的数值不能超过表1最大的NO-7,最终想实现的结果如下:
最后想要的结果:
NO
4
5
6
错误的结果:
NO
4
5
6
8
9
10
表1
NO
1
2
3
7
表2
NO
1
2
3
4
5
6
7
8
9
10
请各位朋友赐教,谢谢!!!
[解决办法]
select [no] from 表2 where [no]<=(select max([no]) as [no] from 表1) and [no] not in(select [no] from 表1)
[解决办法]
select t1.* from t1 left join t2 on t1.no=t2.no where t2.no is null and t1.no!>(select max(no-1) from t1 )
[解决办法]
create table #1(id int)insert into #1 select 1insert into #1 select 2insert into #1 select 3insert into #1 select 7create table #2(id int)insert into #2 select 1 insert into #2 select 2insert into #2 select 3insert into #2 select 4insert into #2 select 5insert into #2 select 6insert into #2 select 7insert into #2 select 8insert into #2 select 9insert into #2 select 10select * from #2 awhere not exists(select 1 from #1 b where a.id=b.id ) and a.id<=(select max(id) from #1) /*--id45*/
[解决办法]
select * from #2 a
where not exists(select 1 from #1 b where a.id=b.id ) and a.id<=(select max(id) from #1)