/* 结构相同的两表，一表有记录3万条左右，一表有记录2万条左右，我怎样快速查找两表的不同记录？*/
-- 给你一个测试方法，从northwind中的orders表取数据。
select * into n1 from orders
select * into n2 from orders
select * from n1
select * from n2
--添加主键，然后修改n1中若干字段的若干条
alter table n1 add constraint pk_n1_id primary key (OrderID)
alter table n2 add constraint pk_n2_id primary key (OrderID)
select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1
/* 应该可以，而且将不同的记录的ID显示出来。下面的适用于双方记录一样的情况，*/
select * from n1 where orderid in (select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)
-- 至于双方互不存在的记录是比较好处理的
--删除n1,n2中若干条记录
delete from n1 where orderID in ('10728','10730')
delete from n2 where orderID in ('11000','11001')
--*************************************************************
-- 双方都有该记录却不完全相同
select * from n1 where orderid in(select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)
union
--n2中存在但在n1中不存的在10728,10730
select * from n1 where OrderID not in (select OrderID from n2)
union
--n1中存在但在n2中不存的在11000,11001
select * from n2 where OrderID not in (select OrderID from n1)
posted on 2009-02-24 14:11
Werther 阅读(239)
评论(0) 编辑 收藏 所属分类:
15.SQL Server