Basic usage of Distinct and GroupBy statements in MySQL _ MySQL

This article mainly introduces the basic usage tutorials of Distinct and GroupBy statements in MySQL. here we mainly deduplicate the query results. For more information, see Remove duplicate query results from MySQL Distinct
DISTINCT
You can use the DISTINCT keyword to remove duplicate records of a field in the query.
Syntax:

SELECT DISTINCT(column) FROM tb_name

Example:
Assume that the user table has the following records:

Uid username1 Xiao Li 2 Xiao Zhang 3 xiao li 4 Xiao Wang 5 xiao li 6 Xiao Zhang

SQL statement:
Select distinct (username) FROM user
The returned query result is as follows:

Username Xiao Li Xiao Zhang Xiao Wang

Prompt
Removing duplicate records using the DISTINCT keyword has great limitations. DISTINCT () can contain only one field, and the query result returns only this field, not the complete data record (as shown in the preceding example ).
Try the following syntax:

SELECT DISTINCT(column),column1,column2,... FROM tb_name

The query results will return all the listed fields, but this query often invalidates the uniqueness of column, and column 1, column 2 ,... It cannot be placed before DISTINCT (column.
In the preceding example, if you want to return the following results (this is often expected ):

Uid username1 Xiao Li 2 Xiao Zhang 3 Xiao Wang

In this case, the group by keyword is used.

MySQL Group By Data Group
GROUP
In MySQL, the group by keyword is used to query a GROUP for a field or some fields, and the first record of repetition is returned.
Syntax:

SELECT column,... FROM tb_name GROUP BY column1,column2 ...

The user table records are as follows:

Uid username1 Xiao Li 2 Xiao Zhang 3 xiao li 4 Xiao Wang 5 xiao li 6 Xiao Zhang

The preceding user table is queried as follows:

SELECT * FROM user GROUP BY username

The returned query result is as follows:

Uid username1 Xiao Li 2 Xiao Zhang 3 Xiao Wang

Description
The usage of group by syntax in MySQL database is quite different from that in other databases. For standard SQL statements, GROUP BY must be used in combination with aggregate functions. in addition to aggregate functions, the selected fields must also appear in GROUP. However, the group by function is extended in MySQL:
Without an aggregate function, the returned result is the first row in the group by result set, as shown in the preceding example.
When group by is combined with aggregate functions, the selected field does not need to exist in group by. MySQL has the implicit field function.
Therefore, we can easily obtain the desired query results based on MySQL's extended features of group by and some other keywords such as order.
Example 2:

SELECT * FROM user GROUP BY username,uid

The returned query result is as follows:

Uid username1 Xiao Li 3 xiao li 5 Xiao Li 2 Xiao Zhang 6 Xiao Zhang 4 Xiao Wang