/* 結構相同的兩表，一表有記錄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 閱讀(247)
評論(0) 編輯 收藏 所屬分類:
15.SQL Server