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) 

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) 

六余分な重複データの取得

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フィールドの左の1番目を削除

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

9フィールドの右側の1番目を削除

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

 
転載先:https://www.cnblogs.com/qiangshu/archive/2013/02/05/2893047.html