There are repetitive records in a table, and the number of repetitions may be one or more, how to leave only one of the repeated records, delete other excess records, so that each record of the dataset is unique? This article uses a method that is more stupid, but the logic is more clear, I hope everyone can provide a better way!
1 list of repeated records in the table
(SameoDA)
SELECT [2]. [Prefecture-level city], count (*) AS record number from 2group by [2]. [Prefecture-level city] haVing count (*)> 1ORDER BY [prefecture)
Minor City Records 10000951094210000095111621000009511210000095114921000009511502
2 The following is the process of removing the same record
1) List all repetition records in the table
(Recorded in Table 2)
Select 2. * from 2 Right Join Sameoda on [2]. [Ground-level city] = [Sameoda]. [Prefecture);
ID number The number of insurance cargo names and packaging unit declared value (yuan) transportation self-transportation to 74691466100000951094 Benzaibette, water fly guest capsule 3 boxes 18600 Tianjin Jiamus 5351107100000951094 Benzazibe tablets, Waterfarefin capsules box 3 18600 Tianjin Jiamusi 53501076100000951116 silibinin capsules tank 5 29000 Tianjin Urumqi 74681465100000951116 silibinin capsules tank 5 29000 Tianjin Urumqi flutamide sheet 53531079100000951127 4 74711468100000951127 box 66800 Tianjin Harbin flutamide sheet box 4 66800 Tianjin Harbin
2) Find a unique record from repeated records
Minbihanhao
SELECT 2. * From 2 LEFT JOIN Table 2 Repeats ON [2]. [ID] = [Table 2 Record in Table 2]. [ID] WHER [Table 2 Record]. [ID] is NULL;
Geni-level city ID10000095109453511000009511165350100000951127535380000000000000000000073853521000000009511505355
3) Display all information about the only record
ALLSAME1
SELECT 2. * from 2 Left Join Minbihanhao on ([2]. [Geopgrad City] = [Minbihanhao]. [Geote - level City]) and ([2]. [ID] = [minbihanhao]. [ID]) Where [minbihanhao]. [ground-level city] is not nullorder by [2]. [ID];
4) Remove all the same records in the original table
Dropsame2
SELECT 2. * From 2 LEFT JOIN Table 2 Repeats ON [2]. [ID] = [Table 2 Record in Table 2]. [ID] WHER [Table 2 Record]. [ID] is NULL;
5) Table 2 Remove the repeated record = All the same record display all the information of the unique record is removed in the original table
Table 2 removes repeated records
Select * from dropsame2 union select * from allsame1;
