The SQL statement used to query multiple fields, query multiple tables, and delete duplicate records.

The SQL statement used to query multiple fields, query multiple tables, and delete duplicate records.

SQL repeat record Query

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) 

Example 2:

select * from testtable where numeber in (select number from people group by number having count(number) > 1 ) 

We can find records with the same number in the testtable table.

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 a 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 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) 

5. Search for redundant duplicate records (multiple fields) in the table, excluding records with the smallest 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) 

(2)

For example

There is A field "name" in Table ",

The "name" value may be the same for different records,

Now, you need to query items with duplicate "name" values between records in the table;

Select Name,Count(*) From A Group By Name Having Count(*) > 1 

If the gender is also the same, the statement is as follows:

Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1 

(3)

Method 1

Declare @ max integer, @ id integer declare cur_rows cursor local for select Main field, count (*) from table name group by main field having count (*)>; 1 open cur_rows fetch cur_rows into @ id, @ max while @ fetch_status = 0 begin select @ max = @ max-1 set rowcount @ max delete from table name where primary field = @ id fetch cur_rows into @ id, @ max end close cur_rows set rowcount 0

Method 2

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)

(4)

Duplicate Query

select * from tablename where id in (  select id from tablename   group by id   having count(id) > 1  ) 

The above is all the content of the SQL repeat record query method for querying multiple fields, querying multiple tables, and deleting duplicate records. I hope it will be helpful to you, support more customers ~