sql server 重复与去重保留一个

select * into #water from dbo.table1 where clo_name='理化'

--创建临时表，并保存数据

select * from ClientMatch where method in

(select method from ClientMath group by method

having COUNT(*)>1)

order by method;

--查询出method相同的数据

 

select * from ClientMath where method in

(select method from ClientMatch group by method

having COUNT(*)>1)

and

testcode in (select testcode from ClientMath group by testconde

having COUNT(*)>1)

--查询method和testcode都重复的数据

 

delete from ClientMacth where

method in (select method from

ClientMatch group by method

having COUNT(*)>1)

and

testcode in (select testcode

from ClientMatch group by testcode

having COUNT(*)>1)

and

ClientMatch.ID not in (select min(ClientMatch.ID) from

ClientMatch group by testcode, method

having COUNT(*)>1)

----删除method和testcode都重复列。可以添加一个ID自增长字段

 

insert into test_methods

(methodcategory,description,method,methodrptname,iscma)

select methodcategory,description,method,methodrptname,iscma from

testcode  where testcode.method not in (select method from test_methods)

----讲一个表中的部分数据插入到另一个表中，method为主键