1. During the interview, I encountered a problem, that is, to write a table with two fields of id and name, query all the data with duplicate names, and now list the following:
2. After querying all the data for grouping, the query data of the number of repetitions of the duplicate data is listed below:
3. The following is to view the results of others, and now the following is listed: A complete list of methods for querying and deleting duplicate records
1. Find the redundant duplicate records in the table, and the duplicate records are judged based on a single field (peopleId).
2. Delete the redundant duplicate records in the table, and the duplicate records are judged based on a single field (peopleId), leaving only the records with the smallest rowid
3. Find redundant duplicate records (multiple fields) in the table
4. Delete the redundant duplicate records (multiple fields) in the table, leaving only the records with the smallest rowid
5. Find redundant duplicate records (multiple fields) in the table, and do not contain records with the least rowid
(two)
For example
There is a field "name" in table A,
And the "name" value may be the same between different records,
Now you need to query for items with duplicate "name" values between records in the table;
If the gender is also the same, it is as follows:
(three)
Method 1
Method 2 "Duplicate records" have two meanings of duplicate records, one is a completely duplicate record, that is, a record with all fields duplicated, and the other is a record with duplicate parts of key fields, such as the Name field is duplicated, while other fields are not necessarily duplicated or all duplicates can be ignored.
1. For the first type of repetition, it is relatively easy to solve and use
You can get the result set with no duplicate records.
If the table needs to delete duplicate records (1 duplicate record is kept),
You can delete it as follows
This duplication occurs due to poor table design and can be solved by adding unique index columns.
2. This kind of duplicate problem usually requires the first record in the duplicate record to be kept, and the operation method is as follows: Assuming that there are duplicate fields as Name and Address, the unique result set of these two fields is required
The last select gets the result set of Name and Address that are not repeated (but there is an additional autoID field, which can be written in the select clause when actually writing)
(4)
Queries are duplicated
|
Posted on 9/15/2018 9:55:18 AM
|
|
|
