SQL Server获取重复数据的方法
一 获取重复的数据的值
select user_name from users group by user_name having(count(1)>1)
二 获取重复数据的记录
select * from users where user_name in ( select user_name from users group by user_name having(count(1)>1) )
三 查找表中多余的重复记录(多个字段)
select * from vitae where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
四 删除表中多余的重复记录,只留有rowid最小的记录
DELETE FROM people WHERE peopleName IN ( SELECT peopleName FROM people GROUP BY peopleName HAVING COUNT(peopleName) > 1 ) AND peopleId NOT IN ( SELECT MIN(peopleId) FROM people GROUP BY peopleName HAVING COUNT(peopleName) > 1 )
五 删除表中多余的重复记录(多个字段),只留有rowid最小的记录
delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
六 获取多余的重复数据
select * from users where user_name not in ( select max(user_name) from users group by user_name having(count(1)>1) )
七 获取多余的重复记录(多个字段),不包含rowid最小的记录
select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
八 消除一个字段的左边的第一位
UPDATE tableName SET [Title] = RIGHT([Title], ( LEN([Title]) - 1 )) WHERE Title LIKE '村%'
九 消除一个字段的右边的第一位
UPDATE tableName SET [Title] = LEFT([Title], ( LEN([Title]) - 1 )) WHERE Title LIKE '%村'