--创建表并填充数据
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