MySQL GROUP BY data grouping--traps

GROUP by

The group BY keyword is used in MySQL to group one or some field queries and returns the first of the duplicate records.

Grammar:

SELECT column,... From Tb_name GROUP by column1,column2 ...

The user table is logged as follows:

UID	username
1	Xiao Li
2	Xiao Zhang
3	Xiao Li
4	Xiao Wang
5	Xiao Li
6	Xiao Zhang

The user table above is queried as follows:

SELECT * from the user GROUP by username

The results of the returned query are as follows:

UID	username
1	Xiao Li
2	Xiao Zhang
3	Xiao Wang

Description

The use of the GROUP by syntax in a MySQL database differs significantly from 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 the aggregate function. However, the functionality of GROUP by IS extended in MySQL:

• Without an aggregate function, the result returned is the first row in the GROUP by result set, as shown in the example above.
• When group by combines aggregate functions, the selected field does not have to exist in group by, and MySQL has the ability to suppress fields.

So we can easily get the desired query results based on MySQL's extended feature of GROUP by, combined with some other keywords such as ORDER by.

Example 2:

SELECT * from the user GROUP by Username,uid

The results of the returned query are as follows:

UID	username
1	Xiao Li
3	Xiao Li
5	Xiao Li
2	Xiao Zhang
6	Xiao Zhang
4	Xiao Wang

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL GROUP BY data grouping--traps