MySQL extracts the sorted data from the table and does not delete duplicate data (1/2)

We need to process repeated data in mysql. Next I will introduce some methods for processing repeated data in mysql, this includes deleting duplicate data, sorting data without duplicates, and querying.

Today, we have a functional requirement to sort out the first ten data records with inconsistent data from the table. For example, the values in Table a include the following:

The Code is as follows:	Copy code
Mysql> select * from; + ---- + ---------- + | Id | user | + ---- + ---------- + | 1 | zhangsan | | 2 | lisi | | 3 | wangwu | | 4 | zhangsan | | 5 | zhaosi | | 6 | wangwu | | 7 | lisi | | 8 | lisi | | 9 | zhaosi | + ---- + ---------- + 9 rows in set (0.00 sec)

We need to retrieve the first four digits with the largest id and the user information is inconsistent. Based on the above information and requirements, we need to search for the result

Zhaosi

Lisi

Wangwu

Zhangsan

You cannot follow common practices, such:

The Code is as follows:	Copy code
Mysql> select * from a order by id desc limit 4; + ---- + -------- + | Id | user | + ---- + -------- + | 9 | zhaosi | | 8 | lisi | | 7 | lisi | | 6 | wangwu | + ---- + -------- + 4 rows in set (0.00 sec)

In this way, the keyword distinct is used to search for duplicate values.

The Code is as follows:	Copy code
Mysql> select distinct user from a order by id desc limit 4; + ---------- + | User | + ---------- + | Zhaosi | | Wangwu | | Lisi | | Zhangsan | + ---------- + 4 rows in set (0.00 sec)

In fact, it is ideal to switch between lisi and wangwu, because lisi has the largest ID of 8, and wangwu has the largest ID of 6, which may be caused by lisi having an ID of 2, let's Delete the ID 2 and try again.

The Code is as follows:

Copy code

Mysql> delete from a where id = 2; Query OK, 1 row affected (0.02 sec)   Mysql> select * from; + ---- + ---------- + | Id | user | + ---- + ---------- + | 1 | zhangsan | | 3 | wangwu | | 4 | zhangsan | | 5 | zhaosi | | 6 | wangwu | | 7 | lisi | | 8 | lisi | | 9 | zhaosi | + ---- + ---------- + 8 rows in set (0.00 sec)   Mysql> select distinct user from a order by id desc limit 4; + ---------- + | User | + ---------- + | Lisi | | Zhaosi | | Wangwu | | Zhangsan | + ---------- + 4 rows in set (0.00 sec)

The result is that the sorting is affected due to a low ID record on the front.

Although this statement can search for the correct results, the sorting may not be so satisfactory, that is, the first four digits of the largest ID can be searched, however, these four data bits are not sorted by ID.

Example 1 Test Data

The Code is as follows:

Copy code

/* Table Structure */ Drop table if exists 't1 '; Create table if not exists 't1 '( 'Id' INT (1) not null AUTO_INCREMENT, 'Name' VARCHAR (20) not null, 'Add' VARCHAR (20) not null, Primary key ('id ') ) Engine = InnoDB; /* Insert Test Data */ Insert into 't1' ('name', 'add') VALUES ('Abc', "123 "), ('Abc', "123 "), ('Abc', "321 "), ('Abc', "123 "), ('Xzy ', "123 "), ('Xzy ', "456 "), ('Xzy ', "456 "), ('Xzy ', "456 "), ('Xzy ', "789 "), ('Xzy ', "987 "), ('Xzy ', "789 "), ('Ijk "," 147 "), ('Ijk "," 147 "), ('Ijk "," 852 "), ('Opq', "852 "), ('Opq', "963 "), ('Opq', "741 "), ('Tpk', "741 "), ('Tpk', "963 "), ('Tpk', "963 "), ('Wher', "546 "), ('Wher', "546 "), ('Server', "546 "); SELECT * FROM 't1 '; + ---- + ------ + ----- + | Id | name | add | + ---- + ------ + ----- + | 1 | abc| 123 | | 2 | abc| 123 | | 3 | abc| 321 | | 4 | abc| 123 | | 5 | xzy | 123 | | 7 | xzy | 456 | | 6 | xzy | 456 | | 8 | xzy | 456 | | 9 | xzy | 789 | 10 | xzy | 987 | | 11 | xzy | 789 | | 12 | ijk | 147 | | 13 | ijk | 147 | 14 | ijk | 852 | | 15 | ops | 852 | | 16 | ops | 963 | | 17 | opq | 741 | | 18 | tprimary | 741 | | 19 | tprimary | 963 | | 20 | tprimary | 963 | | 21 | Lower | 546 | | 22 | Lower | 546 | | 23 | once | 546 | + ---- + ------ + ----- + Rows in set (0.00 sec)

Find the duplicate data with the smallest id (only the id field is queried)

The Code is as follows:	Copy code
/* Find the duplicate data with the smallest id (only the id field is queried )*/ Select distinct min ('id') AS 'id' FROM 't1' Group by 'name', 'add' Having count (1)> 1; + ------ + | Id | + ------ + | 1 | | 12 | | 19 | | 21 | | 6 | | 9 | + ------ + Rows in set (0.00 sec)

Search for all duplicate data

The Code is as follows:

Copy code

/* Search for all duplicate data */ SELECT 't1 '.* FROM 't1 ',( SELECT 'name', 'add' FROM 't1' Group by 'name', 'add' Having count (1)> 1 ) AS 't2' WHERE 't1'. 'name' = 't2'. 'name' AND 't1'. 'add' = 't2'. 'add '; + ---- + ------ + ----- + | Id | name | add | + ---- + ------ + ----- + | 1 | abc| 123 | | 2 | abc| 123 | | 4 | abc| 123 | | 7 | xzy | 456 | | 6 | xzy | 456 | | 8 | xzy | 456 | | 9 | xzy | 789 | 11 | xzy | 789 | | 12 | ijk | 147 | | 13 | ijk | 147 | 19 | tprimary | 963 | | 20 | tprimary | 963 | | 21 | Lower | 546 | | 22 | Lower | 546 | + ---- + ------ + ----- + Rows in set (0.00 sec)

Search for duplicate data with the smallest id

The Code is as follows:

Copy code

/* Search for duplicate data with the smallest id */ SELECT 't1 '.* FROM 't1 ',( Select distinct min ('id') AS 'id', 'name', 'add' FROM 't1' Group by 'name', 'add' Having count (1)> 1 ) AS 't2' WHERE 't1'. 'name' = 't2'. 'name' AND 't1'. 'add' = 't2'. 'add' AND 't1'. 'id' <> 't2'. 'id '; + ---- + ------ + ----- + | Id | name | add | + ---- + ------ + ----- + | 2 | abc| 123 | | 4 | abc| 123 | | 6 | xzy | 456 | | 8 | xzy | 456 | | 11 | xzy | 789 | | 13 | ijk | 147 | 20 | tprimary | 963 | | 22 | Lower | 546 | + ---- + ------ + ----- + Rows in set (0.00 sec)

1 2