SQL server 重复数据

1. 获取重复的数据的值

select user_name from users
group by user_name
having(count(1)>1)

2. 获取重复数据的记录

select * from users where user_name in
(
　　select user_name from users
　　group by user_name
　　having(count(1)>1)
)

3.查找表中多余的重复记录（多个字段）

select * from vitae  
where (a.peopleId,a.seq) 
   in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1) 

4.删除表中多余的重复记录，只留有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 ) 

5.删除表中多余的重复记录（多个字段），只留有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) 

6.获取多余的重复数据

select * from users where user_name not in
(
　　select max(user_name) from users
　　group by user_name
　　having(count(1)>1)
)

7.获取多余的重复记录（多个字段），不包含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)   

8.消除一个字段的左边的第一位

UPDATE  tableName
SET     [Title] = RIGHT([Title], ( LEN([Title]) - 1 ))
WHERE   Title LIKE '村%'

9.消除一个字段的右边的第一位

UPDATE  tableName
SET     [Title] = LEFT([Title], ( LEN([Title]) - 1 ))
WHERE   Title LIKE '%村'