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
|