Detailed explanation of the real opening method of MySql Group by function!

When using the group function to filter the result set, some problems encountered and solutions [Recommended ：mysql video tutorial】

1. Application scenario

There are two tables

Article table (one-to-many messages Table) t_posts:
oid, posts_name
Message table (many-to-one article table) t_comment:
oid, posts_id, msg_content, create_time

2. Requirements analysis

Query the latest reply content of each article

3.SQL writing

select 
  tp.oid,
  tp.posts_name,
  tc.msg_content,
  tc.create_time
from t_posts tp 
left join t_comment tc on tp.oid = tc.posts_id
group by tp.oid having create_time = max(create_time)

Suppose there are now two articles A and B (the order of the reply records in the database is consistent with the following)

<p>A有一个回复记录时间为: 2019-09-10   <br>A有一个回复记录时间为: 2019-09-11   <br>B有一个回复记录时间为: 2019-09-01   <br>B有一个回复记录时间为: 2019-09-09<br></p>

When you run the above sql, you will find that a large number of records are lost in the result set, and the result is Wrong. After querying the data, I learned that

mysql's having is executed after group by. That is to say, grouping is performed first and then filtered. However, because there are more than two message records,
so grouping The subsequent result set will only take the first message of each message as the record information after grouping. If you use having create_time = max(create_time)
, then max(create_time) is the maximum time of the current grouping

is: 2019-09-10 and 2019-09-09

So the above sql will lose the result set

4. Transform SQL

Because we know that the duplicate result set merged after grouping is the one with the smallest rownum, can we modify the sql as follows??

select 
  tp.oid,
  tp.posts_name,
  tc.msg_content,
  tc.create_time
from t_posts tp 
left join t_comment tc on tp.oid = tc.posts_id
group by tp.oid having create_time = max(create_time)
-- 下面的是新增的sql
order by tc.create_time desc

After running it, we found that it still doesn’t work, which proves that order by is in After group by & having

Later I thought about it, can I use order by directly to optimize the grouped results without having?

having create_time = max (create_time)

select 
  tp.oid,
  tp.posts_name,
  tc.msg_content,
  tc.create_time
from t_posts tp 
left join t_comment tc on tp.oid = tc.posts_id
group by tp.oid 
order by tc.create_time desc

The result set error does not affect the grouping results. The duplicate result sets are still merged according to the minimum grouping of rownum, and then sorted

5. Ultimate transformation Version

Because order by can only affect group by, is it possible to sort the result set before group by, and then group it?

select * from (
  select 
    tp.oid,
    tp.posts_name,
    tc.msg_content,
    tc.create_time
  from t_posts tp 
  left join t_comment tc on tp.oid = tc.posts_id
  order by tc.create_time desc
) t 
group by t.oid

I found it still not possible It works, but the subquery is indeed sorted first

After querying (explain), I found that the order by of the subquery has been optimized away. Solution:

1. Use in the subquery limit 99999
2. Use where condition in subquery, create_time = (select max(create_time) from t_comment group by oid)

select * from (
  select 
    tp.oid,
    tp.posts_name,
    tc.msg_content,
    tc.create_time
  from t_posts tp 
  left join t_comment tc on tp.oid = tc.posts_id
  order by tc.create_time desc limit 9999
) t 
group by t.oid

Done

Additional knowledge points:

The difference between mysql5.5 and mysql 5.7 versions: In version 5.7, if limit is not used, group by will optimize order by