Commonly used sql statements

1. Query the latest speech record of each user:

select max(time) from 2017sxgf group by id order by time desc limit 10;

2. Find the user ID and number of times that have spoken the most

select userid,count(userid) from orders where userid != '' group by userid order by count(userid) desc limit 1;

3. Three ways to write 1 record for each user in MySQL

The first is to sort first, and then group, so that you can naturally get it The most suitable piece of data.
The shortcomings are obvious: Using temporary; Using filesort

select * from (select * from 2017sxgf order by time desc)t group by mobile limit 10;

The second is joint query

select * from (select max(time) as btime from 2017sxgf group by mobile limit 10)t left join 2017sxgf as s on t.btime = s.time;

The third is subquery

select * from 2017sxgf where exists(select mobile from (select max(time) as btime from 2017sxgf group by mobile limit 10)t where t.btime = 2017sxgf.time);

5.

The above is the detailed content of Commonly used sql statements. For more information, please follow other related articles on the PHP Chinese website!