删除重复数据 方法

sql如下：

 

create table AllNetCompServUser

(

userid varchar(50), --用户名 

userinfo varchar(100), --用户信息

userorderid int IDENTITY(1,1)  --唯一标识

)

go

 

insert into AllNetCompServUser(userid, userinfo) values ('test1', 'test1 is a good student')

insert into AllNetCompServUser(userid, userinfo) values ('test2', 'test2 is a good student')

insert into AllNetCompServUser(userid, userinfo) values ('test3', 'test3 is a good student')

insert into AllNetCompServUser(userid, userinfo) values ('test4', 'test4 is not a good student')

insert into AllNetCompServUser(userid, userinfo) values ('test1', 'test1 is not a good student')

 

go

 

 

--删除重复数据,注意写法，删除时直接给定别名无法编译

delete  a from  AllNetCompServUser   a 

WHERE  userorderid > 

(SELECT MIN(X.userorderid) FROM AllNetCompServUser X where a.userid = X.userid) --可以使用MAX(X.userorderid)

go

 

select *from AllNetCompServUser   

 

执行结果为：

test1 test1 is a good student 1

test2 test2 is a good student 2

test3 test3 is a good student 3

test4 test4 is not a good student 4

也可以如此：

delete  a from  AllNetCompServUser   a 

WHERE  userorderid <

(SELECT max(X.userorderid) FROM AllNetCompServUser X where a.userid = X.userid) --可以使用MAX(X.userorderid)

 

select *from AllNetCompServUser   

 

执行结果为：

 

test2 test2 is a good student 2

test3 test3 is a good student 3

test4 test4 is not a good student 4

test1 test1 is not a good student 5