将数据库中，表OMRC，FirmName字段，所有重复数据，后面都加字符1
update omrc set FirmName=FirmName+’1’
where FirmName in (select FirmName from omrc group by FirmName having count(FirmName) > 1)
and FirmCode not in (select min(FirmCode) from omrc group by FirmName having count(FirmName )>1)
当然加其它字符也可，用单引号括起来就是
重复超过2次以上的，以查询最多的10次为例，依次分别追加序数改名，如下
update omrc set FirmName=FirmName+''10''
where FirmName in (select FirmName from omrc group by FirmName having count(FirmName) =10)
and FirmCode in (select max(FirmCode) from omrc group by FirmName having count(FirmName )=10)
update omrc set FirmName=FirmName+''9''
where FirmName in (select FirmName from omrc group by FirmName having count(FirmName) =9)
and FirmCode in (select max(FirmCode) from omrc group by FirmName having count(FirmName )=9)
...(876543依次查询追加)
update omrc set FirmName=FirmName+''2''
where FirmName in (select FirmName from omrc group by FirmName having count(FirmName) =2)
and FirmCode in (select max(FirmCode) from omrc group by FirmName having count(FirmName )=2)
第10个名称后追加10，第9个名称后追加9，依次追加，直到没有重复。
列出OMRC中制造商FirmName中重复数据，并按照序号排列
SELECT ROW_NUMBER() OVER(PARTITION BY FirmCode ORDER BY FirmCode FirmName) as NUM,FirmName FROM OMRC
where FirmName in (select FirmName from omrc group by FirmName having count(FirmName) > 1)
and FirmCode not in (select min(FirmCode) from omrc group by FirmName having count(FirmName )>1)
注意运算符、引号等等都是半角，都是一个单引号，如‘9’。出现多个是编辑器问题，每次保存都会自动加一个，如''''''9''''''。
其中
SELECT ROW_NUMBER() OVER(PARTITION BY 重复的字段 ORDER BY 排序字段) AS NUM,重复的字段 FROM 表
重复的数据NUM会按照1、2、3这样排序，不重复的数据NUM为1
|
