SQL finds all of the recorded data in a single table

1, in the interview encountered a problem, is to write a table with ID and name two fields, query out the name duplicate all data, now listed below:

SELECT * FROM Xi a WHERE (a.username) in  (select Username from XI Group by username have  count (*) > 1)

2, the query out all the data after grouping, and repeat the number of repetitions of the query data, first listed below:

Select  count (username) as ' repeat ', username from XI Group by username have  count (*) >1 ORDER BY username desc

3, a look at the results of others, now listed: query and delete duplicate records method Daquan

1, look for redundant records in the table, duplicate records are based on a single field (Peopleid) to determine

SELECT * from Peoplewhere peopleid in (select  Peopleid  from  people  group  by  Peopleid  Having  count (Peopleid) > 1)

2, delete redundant records in the table, duplicate records are based on a single field (Peopleid) to judge, leaving only the smallest ROWID records

Delete from people where Peopleid in  (select  Peopleid from  people  group  by  Peopleid   Have  count (Peopleid) > 1) and rowID not in (select min (rowid) from  people  GROUP by Peopleid have  c Ount (Peopleid) >1)

3. Find redundant duplicate records (multiple fields) in the table

SELECT * from Vitae awhere (a.peopleid,a.seq) in  (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have  Co UNT (*) > 1)

4. Delete extra duplicate records (multiple fields) in the table, leaving only the record with ROWID minimum

Delete from vitae awhere (a.peopleid,a.seq) in  (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1) and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

5. Find redundant duplicate records (multiple fields) in the table, not including the smallest ROWID records

SELECT * from Vitae awhere (a.peopleid,a.seq) in  (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1) and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)

Two

Say
A field "name" exists in table A,
and the "name" value may be the same between different records,
Now is the need to query out the records in the table, "name" value has duplicate entries;

Select Name,count (*) from A Group by Name have Count (*) > 1

If you also look at the same gender, the following is true:

Select Name,sex,count (*) from A Group by Name,sex have Count (*) > 1

Three

Method One

Declare @max integer, @id integerdeclare cur_rows cursor Local for select main field, COUNT (*) from table name Group by main field having count (*) >;open cur_rows fetch cur_rows into @id, @maxwhile @ @fetch_status =0begin    Select @max = @max-1    SET ROWCOUNT @ma x    Delete from table name where main field = @idfetch cur_rows into @id, @maxendclose cur_rowsset rowcount 0

Method Two "duplicate records" have two meanings of duplicate records, one is a completely duplicate records, that is, all the fields are duplicated records, second, some key fields duplicate records, such as the Name field is repeated, and the other fields may not repeat or repeat can be ignored.

1, for the first kind of repetition, easier to solve, using

SELECT DISTINCT * from TableName

You can get a result set with no duplicate records.

If the table needs to delete duplicate records (duplicate records retain 1),

You can delete it as follows

SELECT DISTINCT * to #Tmp from Tablenamedrop table Tablenameselect * to tableName from #Tmpdrop table #Tmp

This duplication occurs because the table is poorly designed and the unique index columns are added to resolve.

2. This type of repetition usually requires the first record in the duplicate record to be retained, as follows, assuming that there is a duplicate field name,address, which requires a result set that is unique to both fields

Select Identity (int,1,1) as Autoid, * to #Tmp from Tablenameselect min (autoid) as autoid to #Tmp2 from #Tmp Group by N Ame,autoidselect * from #Tmp where autoid in (select Autoid from #tmp2)

The last select is the result set that name,address not duplicate (but one more autoid field, which can be written in the SELECT clause without this column in the actual write)

Four
Duplicate query

SELECT * FROM tablename where ID in (select ID from Tablenamegroup by idhaving count (ID) > 1)

SQL finds all of the recorded data in a single table