表t1:
id code listdate sign
1 A00201101020001 2011-01-02 1
2 A00201101030002 2011-01-03 1
3 B00201101010001 2011-01-01 2
4 C00201102050003 2011-02-05 3
5 C00201101300004 2011-01-30 3
6 D00201101180003 2011-01-18 4
7 D00201101180004 2011-01-18 4表t2:
1 A00201101020001 2011-01-02 1
2 A00201101030002 2011-01-03 1
3 B00201101010001 2011-01-01 2
4 C00201102050003 2011-02-05 3
5 C00201101300004 2011-01-30 3
6 D00201101180003 2011-01-18 5我想判断t1和t2的数据是否完全一致,比如t1表比t2表多了t1.id=7的数据,还有t1.id=6的t1.sign=4,而t2.id=6的t1.sign=5,这样就完全不一致性,我以t1表为准来判断t2表,如何来写sql语句?
id code listdate sign
1 A00201101020001 2011-01-02 1
2 A00201101030002 2011-01-03 1
3 B00201101010001 2011-01-01 2
4 C00201102050003 2011-02-05 3
5 C00201101300004 2011-01-30 3
6 D00201101180003 2011-01-18 4
7 D00201101180004 2011-01-18 4表t2:
1 A00201101020001 2011-01-02 1
2 A00201101030002 2011-01-03 1
3 B00201101010001 2011-01-01 2
4 C00201102050003 2011-02-05 3
5 C00201101300004 2011-01-30 3
6 D00201101180003 2011-01-18 5我想判断t1和t2的数据是否完全一致,比如t1表比t2表多了t1.id=7的数据,还有t1.id=6的t1.sign=4,而t2.id=6的t1.sign=5,这样就完全不一致性,我以t1表为准来判断t2表,如何来写sql语句?
if exists (select * from t2
where not exists (select 1 from t1 where id = t2.id and code = t2.code
and listdate = t2.listdate and sign = t2.sign)
)
print '不一致'
else
print '一致'
if exists(select 1 from (select * from t2 except select * from t1) a)
print '不一致'
else
print '一致'
use tempdb;
/*
create table t1
(
id int not null,
code nvarchar(20) not null,
listdate date not null,
[sign] int not null
);
insert into t1(id,code,listdate,[sign])
values
(1,'A00201101020001','2011-01-02',1),
(2,'A00201101030002','2011-01-03',1),
(3,'A00201101010001','2011-01-01',2),
(4,'A00201102050003','2011-02-05',3),
(5,'A00201101300004','2011-01-30',3),
(6,'A00201101180003','2011-01-18',4),
(7,'A00201101180004','2011-01-18',4);create table t2
(
id int not null,
code nvarchar(20) not null,
listdate date not null,
[sign] int not null
);
insert into t2(id,code,listdate,[sign])
values
(1,'A00201101020001','2011-01-02',1),
(2,'A00201101030002','2011-01-03',1),
(3,'A00201101010001','2011-01-01',2),
(4,'A00201102050003','2011-02-05',3),
(5,'A00201101300004','2011-01-30',3),
(6,'A00201101180003','2011-01-18',5);
*/
select
case COUNT(*)
when 0 then '完全一致' else '不一致'
end as [比较结果]
from
(
(select * from t1)
except
(select * from t2)
) as t;
declare @表t1 table (id int,code varchar(15),listdate datetime,sign int)
insert into @表t1
select 1,'A00201101020001','2011-01-02',1 union all
select 2,'A00201101030002','2011-01-03',1 union all
select 3,'B00201101010001','2011-01-01',2 union all
select 4,'C00201102050003','2011-02-05',3 union all
select 5,'C00201101300004','2011-01-30',3 union all
select 6,'D00201101180003','2011-01-18',4 union all
select 7,'D00201101180004','2011-01-18',4declare @表t2 table (id int,code varchar(15),listdate datetime,sign int)
insert into @表t2
select 1,'A00201101020001','2011-01-02',1 union all
select 2,'A00201101030002','2011-01-03',1 union all
select 3,'B00201101010001','2011-01-01',2 union all
select 4,'C00201102050003','2011-02-05',3 union all
select 5,'C00201101300004','2011-01-30',3 union all
select 6,'D00201101180003','2011-01-18',5select * from @表t1 a full join
@表t2 b on a.id=b.id and a.code=b.code and a.listdate=b.listdate and a.sign=b.sign
/*
id code listdate sign id code listdate sign
----------- --------------- ----------------------- ----------- ----------- --------------- ----------------------- -----------
1 A00201101020001 2011-01-02 00:00:00.000 1 1 A00201101020001 2011-01-02 00:00:00.000 1
2 A00201101030002 2011-01-03 00:00:00.000 1 2 A00201101030002 2011-01-03 00:00:00.000 1
3 B00201101010001 2011-01-01 00:00:00.000 2 3 B00201101010001 2011-01-01 00:00:00.000 2
4 C00201102050003 2011-02-05 00:00:00.000 3 4 C00201102050003 2011-02-05 00:00:00.000 3
5 C00201101300004 2011-01-30 00:00:00.000 3 5 C00201101300004 2011-01-30 00:00:00.000 3
6 D00201101180003 2011-01-18 00:00:00.000 4 NULL NULL NULL NULL
7 D00201101180004 2011-01-18 00:00:00.000 4 NULL NULL NULL NULL
NULL NULL NULL NULL 6 D00201101180003 2011-01-18 00:00:00.000 5
*/
except
select *,'record only in t2' as [Desc] from t1)
union
(select *,'record only in t1 ' as [Desc] from t1
except
select *,'record only in t1' as [Desc] from t2)
if exists (select * from ((select * from t1) except (select * from t2)) as t)
print '不一致'
else
print '一致'
select * from t1 where checksum(*) not in
(select checksum(*) from t2)
如何用一句SQL返回表A中存在的id,name结果集而在表B中不存在的id,name结果集select A.* from A left join B on A.id=B.id and A.name=B.name where B.id is nullselect * from A where not exists(select top 1 * from B where A.ID=B.ID)这两个都可以.
--前提:表中不能有text、ntext、image、cursor 数据类型的字段。用CheckSum()最简单:select * from A where checksum(*) not in (select checksum(*) from B)