--创建表并填充数据
create table b_dist(id int,name varchar(20), dept varchar(20))
insert into b_dist values(1,‘abc‘,‘sales‘)
insert into b_dist values(2,‘abc‘,‘sales‘)
insert into b_dist values(3,‘abc‘,‘market‘)
insert into b_dist values(4,‘abc‘,‘sales‘)
insert into b_dist values(5,‘abc‘,‘infos‘)
insert into b_dist values(6,‘abc‘,‘market‘)
insert into b_dist values(7,‘abc‘,‘backup‘)
--使用游标逐个比较, 把关键的字段要排序
declare @id int
declare @name varchar(20)
declare @dept varchar(20)
declare @prevname varchar(20)
declare @prevdept varchar(20)
declare cur cursor for select id, name, dept from b_dist order by name, dept
open cur
fetch next from cur into @id, @name, @dept
while @@FETCH_STATUS = 0
begin
if (@dept=@prevdept) and (@name=@prevname)
delete from b_dist where id=@id
select @prevname=@name, @prevdept=@dept
fetch next from cur into @id, @name, @dept
end
close cur
deallocate cur
转自:http://www.cnblogs.com/qanholas/archive/2010/11/25/1888115.html