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为主键