access| Repeat | repeat record
There are duplicate records in one table, the number of repetitions may be one or more, how to leave only one in the duplicate records, delete other redundant records, so that each record of the dataset is unique? This article uses a relatively clumsy but logical more clear method, hope that we can provide a better way!
1 list duplicate records in a table
(Sameoda)
SELECT [2]. [prefecture-level], COUNT (*) as record number from 2GROUP by [2]. Cities Having Count (*) >1order by [prefecture-level];
Record number of prefecture-level cities 100000951094210000095111621000009511272100000951138210000095114921000009511502
2 The following is the process of removing the same record
1 List all duplicate records in the table
(Duplicate records in table 2)
SELECT 2.*from 2 Right JOIN Sameoda on [2]. [prefecture-level]=[sameoda]. [prefecture-level];
ID Number prefecture-level insurance goods name quantity and packing unit declaration value (yuan) transport from transport to 74691466100000951094 benzene bezafibrate tablets, silymarin capsules 3 cases 18600 Tianjin Jiamusi 53511077100000951094 benzene bezafibrate tablets, Water Silymarin Capsules 3 cases 18600 Tianjin Jiamusi 53501076100000951116 Water Silymarin Capsules 5 cases 29000 Tianjin Urumqi 74681465100000951116 Water silymarin Capsule 5 case 29000 Tianjin Urumqi 53531079100000951127 fluoro Amine Slice 4 Box 66800 Tianjin Harbin 74711468100000951127 fluoro Amine Film 4 case 66800 Tianjin Harbin
2 find only one record from the duplicate record
Minbihanhao
In the SELECT 2.*from 2 left JOIN table 2, Duplicate records on [2]. [Duplicate records in id]=[table 2]. [ID] WHERE [Duplicate records in table 2]. [id] is Null;
SELECT 2.*from 2 left JOIN Minbihanhao on ([2].[ Prefecture-level]=[minbihanhao]. [prefecture-level]) and ([2].[ Id]=[minbihanhao]. [ID]) WHERE [Minbihanhao]. Cities is isn't nullorder by [2]. [ID];
4 Remove all the same records in the original table
Dropsame2
In the SELECT 2.*from 2 left JOIN table 2, Duplicate records on [2]. [Duplicate records in id]=[table 2]. [ID] WHERE [Duplicate records in table 2]. [id] is Null;
5) Table 2 Remove duplicate records = Drop all the same records in the original table + all information showing unique records
Table 2 removing duplicate records
Select *from dropsame2 UNION select * from allsame1;
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.