MySQL Distinct Delete Query results duplicate records
DISTINCT
Use the DISTINCT keyword to remove duplicate records for a field in a query.
Grammar:
SELECT DISTINCT (column) from Tb_name
Example:
Assume that the user table has the following record:
UID username
1 Xiao Li
2 xiao Zhang
3 xiao Li
4 xiao Wang
5 xiao Li
6 Xiao Zhang
SQL statement:
SELECT DISTINCT (username) from user
Return query results as follows:
Username Xiao
Li
Xiao Zhang
Xiao Wang
Tips
Using the DISTINCT keyword to remove duplicate records has great limitations. DISTINCT () can only contain one field and the query result returns only that field, not the complete record of the data (as shown in the previous example).
You can try using the following syntax:
SELECT DISTINCT (column), Column1,column2,... From Tb_name
The query results will return all the fields listed, but the query often invalidates column uniqueness and column 1,column 2,... cannot be placed before DISTINCT (column).
The example above should return the following result (which is often expected):
UID username
1 Xiao Li
2 xiao Zhang
3 Xiao Wang
The GROUP by keyword will be used at this time.
MySQL GROUP BY Data grouping
GROUP by
MySQL uses the group by keyword to group queries for one or some of the fields, and returns the first of the duplicate records.
Grammar:
SELECT column,... From Tb_name GROUP by column1,column2 ...
The user table records are as follows:
UID username
1 Xiao Li
2 xiao Zhang
3 xiao Li
4 xiao Wang
5 xiao Li
6 Xiao Zhang
Check the user table above for the following:
SELECT * from user GROUP by username
Return query results as follows:
UID username
1 Xiao Li
2 xiao Zhang
3 Xiao Wang
Description
The use of the GROUP by syntax in the MySQL database differs greatly from that of other databases. For standard SQL, group by must be used in conjunction with aggregate functions, and the selected field must appear in GROUP by, in addition to aggregate functions. However, the functionality of GROUP by IS extended in MySQL:
Without the aggregate function, the result returned is the first row in the GROUP by result set, as shown in the previous example.
When group by combines aggregate functions, the selected fields do not have to exist in GROUP by, and MySQL has the ability to suppress fields.
So we can according to MySQL on the GROUP by the extended characteristics, combined with some other keywords such as order by and so on, convenient to get the desired results of the query.
Example 2:
SELECT * from user GROUP by Username,uid
Return query results as follows:
UID username
1 Xiao Li
3 xiao Li
5 xiao Li
2 xiao Zhang
6 xiao Zhang
4 Xiao Wang