分享链接:
http://blog.csdn.net/s630730701/article/details/52033018
http://blog.csdn.net/anya/article/details/6407280/
http://www.myexception.cn/sql-server/350450.html
http://blog.csdn.net/u012889638/article/details/46893855
http://bbs.csdn.net/topics/370190752
暂不直接说删除问题,先说明下sqlserver不支持多字段in。举个栗子:
select *
from vitae a
where (a.peopleId,a.seq)
in (select peopleId,seq
from vitae
group by peopleId,seq
having count(*) > 1)
这种在sqlserver中就会报语法错误。
解决方案:(1)
DELETE dbo.t_test
from dbo.t_test A
WHERE A.b IN (select b
from dbo.t_test b group by b,c having COUNT(*)>1)
AND A.C IN (select c
from dbo.t_test b group by b,c having COUNT(*)>1)
and id not in(select MIN(id) from dbo.t_test group by b,c having COUNT(1)>1)
注意使用这个语句当数据量很大或者字段很多的时候会运行不了,太浪费时间
解决方案(2)
DELETE A
--select A.*
FROM dbo.t_test A ,dbo.t_test B
WHERE A.b=B.b AND A.c=B.c AND A.id !=B.id
and A.id NOT IN(select min(id) from dbo.t_test group by b,c)
我们写的 delete table where 和update table 都属于简写,全写应该是
delet A FROM TABLE A WEHRE
参考链接:http://blog.csdn.net/zyzlywq/article/details/6753648
注意delete 删除的表是紧跟在后面的表 A,在from中可以引入另一个表进行关联
我目前阶段最大的感受就是:版本问题,这真的很重要。再举个小栗子sqlserver不能使用limit
(3)使用distinct筛选出不重复的记录,保存,在删除原来的数据库。(这个我没试过,应该可以)