Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to query duplicate records in SQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

1. Look up the redundant duplicate records in the table. Duplicate records are based on a single field (peopleId) to determine the copy code as follows: select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count)

(peopleId) > 1)

2. Delete redundant duplicate records from the table. Duplicate records are judged according to a single field (peopleId). The copy code for the record with the smallest rowid is as follows: 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. The copy code for redundant duplicate records (multiple fields) in the lookup table is as follows: select * from vitae awhere (a.peopleIdmema.seq) in (select peopleId,seq from vitae group by peopleId,seq having)

Count (*) > 1)

4. Delete the redundant duplicate records (multiple fields) in the table, leaving only the smallest record in rowid. The copy code is as follows: delete from vitae awhere (a.peopleIdMagna .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. Look up the redundant duplicate records (multiple fields) in the look-up table, and the minimum record replication code excluding rowid is as follows: select * from vitae awhere (a.peopleIdmema.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 A, and the value of "name" may be the same between different records. Now it is necessary to query the items with duplicate "name" values among the records in the table. The copy code is as follows: Select Name,Count (*) From A Group By Name Having Count (*) > 1

If you also check that the gender is the same, the following is the copy code: Select Name,sex,Count (*) From A Group By Name,sex Having Count (*) > 1

(3) the copy code for method 1 is as follows: declare @ max integer,@id integerdeclare cur_rows cursor local for select main field, count (*) from table name group by main field having

Count (*) >; 1open cur_rowsfetch cur_rows into @ id,@maxwhile @ @ fetch_status=0beginselect @ max = @ max-1set rowcount @ maxdelete from table name where main field = @ idfetch cur_rows into @ id,@maxendclose cur_rowsset rowcount 0

Method two

There are two duplicate records, one is a completely duplicated record, that is, a record in which all fields are duplicated, and the other is the duplication of some key fields.

Duplicate records, such as the Name field, are duplicated, while other fields are not necessarily duplicated or can be ignored.

1. For the first kind of repetition, it is relatively easy to solve. The copy code is as follows: select distinct * from tableName

You can get a result set with no duplicate records.

If the table needs to delete duplicate records (keep 1 duplicate record), you can delete the copy code as follows: select distinct * into # Tmp from tableNamedrop table tableNameselect * into tableName from # Tmpdrop table # Tmp

The reason for this repetition is the poor design of the table, which can be solved by adding unique index columns.

2. This kind of duplicate problem usually requires the retention of the first record in the duplicate record. The operation method is as follows.

Suppose a duplicate field is Name,Address, and the copy code for the unique result set of these two fields is as follows: select identity (int,1,1) as autoID, * into # Tmp from tableNameselect min (autoID) as autoID into # Tmp2 from # Tmp group by Name,autoIDselect * from # Tmp where autoID in (select autoID from # tmp2)

The last select results in a result set in which Name,Address does not repeat (but with an extra autoID field that can be written when actually writing

Omit this column in the select clause)

(4) the code for repeated copy of the query is as follows: select * from tablename where id in (select id from tablename group by id having count (id) > 1)

After reading the above, do you have any further understanding of how to query duplicate records in SQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.