随笔-199  评论-203  文章-11  trackbacks-0

     /* 结构相同的两表,一表有记录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 阅读(229) 评论(0)  编辑  收藏 所属分类: 15.SQL Server

只有注册用户登录后才能发表评论。


网站导航: