select * from a where not exists(select 1 from b where a.id=b.id)id是主键
如何从表A(总表)中找出表B(分表)中不存在的记录组合假设表A和表B都只有两个字段id,name 如何用一句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)
select * from A except select * from B
select * from a where not exists (select * from a intersect select * from b)
...都差吧...1.全部重复 select * from a except select * from b 2.部分重复 select * from a where not exists(select * from b where a.id=id) --id不重复的字段
use tempdb; /* create table A ( username nvarchar(20) not null, userage int not null );create table B ( username nvarchar(20) not null, userage int not null );insert into A(username,userage) values('aa',10),('bb',20),('cc',30); insert into B(username,userage) values('aa',10),('cc',30); */ --方法1 select A.username,A.userage from A where not exists ( select B.username,B.userage from B where A.username = B.username and A.userage = B.userage ); --方法2 (select A.username,A.userage from A) except (select B.username,B.userage from B);
2005里的代码 select * from a except select * from b 2000里的代码 select * from a where not exists(select * from b where a.id=id and ...) --where条件里写上所有比较。
select * from A where checksum(*) not in (select checksum(*) from B)这个学习了
如何用一句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)
except
select * from B
select *
from a
where not exists (select * from a
intersect
select * from b)
select * from a
except
select * from b 2.部分重复
select * from a
where not exists(select * from b where a.id=id) --id不重复的字段
use tempdb;
/*
create table A
(
username nvarchar(20) not null,
userage int not null
);create table B
(
username nvarchar(20) not null,
userage int not null
);insert into A(username,userage) values('aa',10),('bb',20),('cc',30);
insert into B(username,userage) values('aa',10),('cc',30);
*/
--方法1
select A.username,A.userage from A
where not exists
(
select B.username,B.userage from B
where A.username = B.username
and A.userage = B.userage
);
--方法2
(select A.username,A.userage from A)
except
(select B.username,B.userage from B);
select * from a
except
select * from b 2000里的代码
select * from a
where not exists(select * from b where a.id=id and ...) --where条件里写上所有比较。