假如 tb1id time
1 9:01
2 9:11
3 9:13tb2 id time
3 9:12
1 10:01
2 10:11
只有三条记录,如何确定 tb1 tb2 内容相当: id 一致,time 可以不一致。
1 9:01
2 9:11
3 9:13tb2 id time
3 9:12
1 10:01
2 10:11
只有三条记录,如何确定 tb1 tb2 内容相当: id 一致,time 可以不一致。
not exists(select 1 from tb2 where id not in (select id from tb1))
print '一致'
else
print '不一致'
select * from tb1 a where exists(select 1 from tb2 where id=a.id)
if object_id('tempdb.dbo.#tb1') is not null drop table #tb1
go
create table #tb1 (id int,time datetime)
insert into #tb1
select 1,'9:01' union all
select 2,'9:11' union all
select 3,'9:13'
--> 测试数据: #tb2
if object_id('tempdb.dbo.#tb2') is not null drop table #tb2
go
create table #tb2 (id int,time datetime)
insert into #tb2
select 3,'9:12' union all
select 1,'10:01' union all
select 2,'10:11'if exists(
select id from #tb1
except
select id from #tb2
union all
select id from #tb2
except
select id from #tb1 )print '不一致'else
print '一致'
where not exists(select 1 from tb2 b where b.id = a.id)
union all
select '表2中有而表1中没有的' as title,* from tb2 a
where not exists(select 1 from tb1 b where b.id = a.id)
select 1
from (select rid=row_number() over(order by id),* from tb1) a
full join (select rid=row_number() over(order by id),* from tb2 ) b
on a.rid = b.rid and a.id = b.id
where a.id is null or b.id is null
)
print '不一致'
else
print '一致'
print '一致'
if exists (select 1 from tb1 where id not in(select id from tb2))
print '不一致'
else
print '一致'
if exists (select 1 from tb1 where id not in(select id from tb2))
and exists(select 1 from tb2 where id not in(select id from tb1))
print '不一致'
else
print '一致'
or exists(select 1 from tb2 where id not in(select id from tb1))
print '不一致'
else
print '一致'