Home >Database >Mysql Tutorial >删除重复记录,并且剩下一条

删除重复记录,并且剩下一条

WBOY
WBOYOriginal
2016-06-07 17:57:49840browse

所谓的重复记录就是某列数据有重复的,但是在实际的过程中,有可能您的需求是一列有重复,也可能两列有重复..根据实际情况,所得到的重复记录是有所不同的

我们可以通过下述方法删除重复记录:
例:表名:dbo.品种描述$,字段包括:ID_PK,品种名称,性状标准编号,代码,首先创建一个和原表结构一样的表:
代码如下:
select * into tmpA from dbo.品种描述$ where 1=2--创建完毕
在数据表中,品种名称,性状标准编号这两个字段不能有重复值,执行下述脚本:

declare @VarietyName nvarchar(255),
@StdCharCode nvarchar(255),
@iCount int
set @iCount=0;
declare insert_distinct_cursor cursor for
select 品种名称,性状标准编号 from dbo.品种描述$ group by 品种名称,性状标准编号
open insert_distinct_cursor
FETCH NEXT FROM insert_distinct_cursor INTO @VarietyName,@StdCharCode
WHILE (@@fetch_status -1)
BEGIN
IF (@@fetch_status -2)
BEGIN
insert into dbo.tmpA (品种名称,性状标准编号,代码) select top 1 品种名称,性状标准编号,代码 from dbo.品种描述$ where 品种名称=@VarietyName and 性状标准编号=@StdCharCode;
set @iCount=@iCount+1;
END
FETCH NEXT FROM insert_distinct_cursor INTO @VarietyName,@StdCharCode
END

CLOSE insert_distinct_cursor
DEALLOCATE insert_distinct_cursor
print @iCount
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn