会员数据需要用到的是下面三个字段:ID(自增),MemberName,MemberAddress.只要会员姓名与会员地址相同就认为是重复记录,重复记录在删除时只保留ID最大的那个.SQL如下:
delete MemberInfo where ID not in (
select max(ID) from MemberInfo group by MemberName, MemberAddress)
not in的效率可能会低些,但因为是直接操作数据库,所以这并不重要.这个句子还是非常的简单有效的.
在真正的删除操作前,通常会先了解一下重复记录的情况.可以使用下面的句子:
SELECT COUNT(MemberName) AS TheCount, MemberName, MemberAddress
FROM MemberInfo
GROUP BY MemberName, MemberAddress
HAVING (COUNT(*) > 1)
delete a from tb a where exists(select 1 from tb where name=a.name and ID<a.ID)
delete from tb where id not in (select min(id) from tb b where name=tb.name)
有一个表转换成功,另外有一个不能有Distinct因为有字段的数据类型是Ntext.
ERROR MESSAGE:
Msg 421, Level 16, State 1, Line 1
The ntext data type cannot be selected as DISTINCT because it is not comparable.