MySQL takes out the sorted data in the table and does not delete duplicate data (1/2)

To do a functional requirement today, sort out the first 10 and inconsistent data from the table, such as the following values in table A:

The code is as follows	Copy Code
Mysql> select * from A; +----+----------+ | ID | user | +----+----------+ | 1 | Zhangsan | | 2 | Lisi | | 3 | Wangwu | | 4 | Zhangsan | | 5 | Zhaosi | | 6 | Wangwu | | 9 2 Lisi | | 8 | Lisi | | 9 | Zhaosi | +----+----------+ 9 Rows in Set (0.00 sec)

We need to remove the top four ID, and user inconsistent information, according to the above information and requirements, we want to search for results

Zhaosi

Lisi

Wangwu

Zhangsan

Not in accordance with ordinary practice, such as:

The code is as follows	Copy Code
Mysql> SELECT * from an ORDER BY id desc limit 4; +----+--------+ | ID | user | +----+--------+ | 9 | Zhaosi | | 8 | Lisi | | 7 | Lisi | | 6 | Wangwu | +----+--------+ 4 rows in Set (0.00 sec)

So the search comes out with duplicate values, you have to use the DISTINCT keyword

The code is as follows	Copy Code
Mysql> SELECT DISTINCT user from an ORDER by id desc limit 4; +----------+ | user | +----------+ | Zhaosi | | Wangwu | | Lisi | | Zhangsan | +----------+ 4 rows in Set (0.00 sec)

In fact, it should be Lisi and Wangwu interchange is more ideal, because the Lisi the largest ID is 8, and WANGWU the largest ID is 6, may be Lisi has an ID of 2 caused, we put the ID 2 of the deletion, in to try

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 A; +----+----------+ | 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 an ORDER by id desc limit 4; +----------+ | user | +----------+ | Lisi | | Zhaosi | | Wangwu | | Zhangsan | +----------+ 4 rows in Set (0.00 sec)

The result is that a lower ID record precedes the sort.

Although this statement can search for the correct effect, it is possible that the sort is not ideal, that is, the first four digits with the largest id can be searched, but they are not sorted by ID size in these four-bit data.

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 not NULL, ' Add ' VARCHAR 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 '), (' wer ', ' 546 '), (' Wer ', "546"), (' Once ', ' 546 '); SELECT * from ' T1 '; +----+------+-----+ | ID | name | Add | +----+------+-----+ | 1 | ABC | 123 | | 2 | ABC | 123 | | 3 | ABC | 321 | | 4 | ABC | 123 | | 5 | Xzy | 123 | | 6 | Xzy | 456 | | 7 | Xzy | 456 | | 8 | Xzy | 456 | | 9 | Xzy | 789 | | 10 | Xzy | 987 | | 11 | Xzy | 789 | | 12 | Ijk | 147 | | 13 | Ijk | 147 | | 14 | Ijk | 852 | | 15 | OPQ | 852 | | 16 | OPQ | 963 | | 17 | OPQ | 741 | | 18 | TPK | 741 | | 19 | TPK | 963 | | 20 | TPK | 963 | | 21 | Wer | 546 | | 22 | Wer | 546 | | 23 | Once | 546 | +----+------+-----+ Rows in Set (0.00 sec)

Find duplicate data with least ID (Find ID field only)

The code is as follows	Copy Code
/* Find duplicate data with minimal ID (Find ID field only) * * 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)

Find all duplicate data

The code is as follows

Copy Code

/* Find all duplicate data * / Select ' T1 '. * from ' T1 ', (   Select ' name ', ' Add '   ' 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 | |  6 | xzy  | 456 | |  7 | xzy  | 456 | |  8 | xzy  | 456 | |  9 | xzy  | 789 | | xzy  | 789 | | ijk  | 147 | | ijk  | 147 | | tpk  | 963 | | tpk  | 963 | | wer  | 546 | | wer  | 546 | +----+------+-----+  rows in Set (0.00 sec)

Find duplicate data In addition to the least ID

The code is as follows

Copy Code

/* Find duplicate data In addition to the least ID data * * 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 | | 7 | Xzy | 456 | | 8 | Xzy | 456 | | 11 | Xzy | 789 | | 13 | Ijk | 147 | | 20 | TPK | 963 | | 22 | Wer | 546 | +----+------+-----+ Rows in Set (0.00 sec)

Home 1 2 last page