Repeated SQL query column (field) values and operations-sorting

Method 1:

1. Search for redundant duplicate records in the Table. duplicate records are determined based on a single field (peopleid ).
Select * from people
Where peopleid in (select peopleid from people group by peopleid having count (peopleid)> 1)
2. Delete unnecessary duplicate records in the Table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid
Delete from people
Where peopleid in (select peopleid from people group by peopleid having count (peopleid)> 1)
And rowid not in (select Min (rowid) from people group by peopleid having count (peopleid)> 1)
3. Search for redundant duplicate records in the table (multiple fields)
Select * From vitae
Where (A. peopleid, A. seq) in (select peopleid, seq from vitae group by peopleid, seq having count (*)> 1)
4. Delete redundant record (multiple fields) in the table, leaving only the records with the smallest rowid
Delete from vitae
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)
5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest rowid
Select * From vitae
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)

 

Method 2:

Now, assume that the database table is named student and contains the Sno (student ID), ID (ID ),You need to find records with repeated SnO fields. The ID field is unique.Two query methods are provided here:

 

1: query by student ID and ID card fields (highly efficient database execution, recommended)

Select *
From student as
Where (SnO IN (select SnO from
Student as B where a. ID <> B. ID ))

2: query by student ID field only (Database execution efficiency is low, not recommended)

Select *
From student as
Where (select count (*) from
Student where SnO = A. SnO)> 1

From the running results, 1 and 2 are the same, but the execution efficiency is completely different. It takes only a few seconds for method 1 to execute more than 10 thousand pieces of data. method 2 takes several minutes and crashes.

 

Method 3:

There are two Repeated Records. One is a completely repeated record, that is, records with all fields being repeated, and the other is records with duplicate key fields, such as duplicate name fields, other fields are not necessarily repeated or can be ignored.

1. For the first type of repetition, it is easier to solve.

Select
Distinct * from
Tablename

You can get the result set without repeated records.

If the table needs to delete duplicate records (one record is retained), you can delete the record as follows:

Select
Distinct * into # TMP from tablename

Drop table tablename

Select *
Into tablename from # TMP

Drop table
# TMP

The reason for this repetition is that the table design is not weekly. You can add a unique index column.

2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows:

Assume that the duplicate fields are Name and address. You must obtain the unique result set of the two fields.

Select
Identity (INT, 1, 1) as autoid, * into # TMP from tablename

Select
Min (autoid) as autoid into # tmp2 from # TMP group by name, autoid

Select *
From # TMP where autoid in (select autoid from
# Tmp2)

The last SELECT command gets the result set with no duplicate name and address (but an autoid field is added, which can be omitted in the select clause when writing)

 

Supplement:

there are more than two duplicate records. One is a completely repeated record, that is, a record with all fields already exists. The other is a record with duplicate key fields, for example, the name field already exists, other fields are not necessarily repeated or can be ignored.
1. For the first type of repeat, it is easy to solve. You can use
select distinct * From tablename
to obtain the result set without repeated records.
If the table needs to delete duplicate records (one record is retained ), you can delete
select distinct * into # TMP from tablename
drop table tablename
select * into tablename from # TMP
drop table # TMP
the reason for this repetition is that the table design is not weekly, you can add a unique index column.
2. Repeat problems generally require that the first record in the repeat record be retained. The procedure is as follows
assume that the duplicate field is name, address, obtain the unique result set of the two fields.
Select Identity (INT,) as autoid, * into # TMP from tablename
select Min (autoid) as autoid into # tmp2 from # TMP group by name, autoid
select * from # TMP where autoid in (select autoid from # tmp2)
name and address are obtained in the last SELECT statement (but an autoid field is added, which can be omitted in the select clause in actual writing).