Mysql database group by study notes

The mysql GROUP command is group by. Next I will share my notes when studying the group by command. Here I will discuss the simple usage of GROUP BY and GROUP BY sorting.

The group statement group by is used whenever you want to know the number of classes or find different types of databases.

Usage:

The Code is as follows:	Copy code
SELECT * FROM 'table name' group by 'grouping field'

Or use limit

The Code is as follows:	Copy code
SELECT * FROM 'data table' Group by 'grouping field' LIMIT 0, 30

Example: (query the topic title table of the dedecms program and group it by topic id)

The Code is as follows:	Copy code
SELECT * FROM 'dede _ archives' Group by 'typeid' LIMIT 0, 30

Mysql group by sorting.

For example, if there is a post reply table, posts (id, tid, subject, message, dateline ),

Id is the auto-increment field, tid is the id (foreign key Association) of the reply topic post, subject is the reply title, message is the reply content, dateline is the reply time, expressed in UNIX timestamp,

Simplest:

The Code is as follows:	Copy code
SELECT * FROM (SELECT * FROM posts order by dateline DESC) group by tid order by dateline desc limit 10

Some netizens also use self-connection to achieve higher efficiency than the preceding subquery. However, to be simple and clear, only such a method is used. group by has no sorting function, maybe it's something that mysql is mentally retarded, maybe it's something I haven't found,

Where + group by (sort groups)

I only find that group_concat () can be used to sort groups in group by, but group_concat is used to concatenate values in fields in the group.

The Code is as follows:	Copy code
Select group_concat (id order by 'date' desc) from 'test' group by category_id

The Code is as follows:	Copy code
Select * from 'test' where id in (select SUBSTRING_INDEX (group_concat (id order by 'date' desc), ',', 1) from 'test' group by category_id) order by 'date' desc

Subquery Solution

The Code is as follows:	Copy code
Select * from (select * from 'test' order by 'date' desc) 'temp 'group by category_id order by 'date' desc