Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of how to use the MySql Group by function correctly, the content is detailed and easy to understand, the operation is simple and fast, and has a certain reference value, I believe you will gain something after reading this article on how to use the MySql Group by function correctly, let's take a look at it.

The correct way to open the MySql Group by function

When using grouping function, filter the result set, some problems encountered and the solutions.

1. Application scenario

There are two tables.

Article table (one-to-many message table) t_posts:

Oid, posts_name

Message form (many to one article table) t_comment:

Oid, posts_id, msg_content, create_time

two。 Demand analysis

Query the latest responses to each article

3.SQL writing

Select tp.oid, tp.posts_name, tc.msg_content, tc.create_timefrom t_posts tp left join t_comment tc on tp.oid = tc.posts_idgroup by tp.oid having create_time = max (create_time)

Suppose there are two articles An and B (the order of the replied records in the database is the same as the following)

A there is a reply recorded at 2019-09-10.

A there is a reply recorded at 2019-09-11.

B there is a reply recorded at: 2019-09-01

B there is a reply recorded at 2019-09-09.

When you run the sql above, you will find that a large number of records have been lost in the result set, and the result is incorrect. After querying the data, you will know

The having of mysql is executed after group by, that is, grouping first and filtering, but because there are more than two messages recorded.

So the result set after grouping will only take the first message of each message as the recording information after grouping. If you use having create_time = max (create_time)

Then, max (create_time) is the maximum time for the current packet

Are: 2019-09-10 and 2019-09-09

So the above sql will lose the result set

4. Revamping SQL

Knowing that the merged repeated result set 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_timefrom t_posts tp left join t_comment tc on tp.oid = tc.posts_idgroup by tp.oid having create_time = max (create_time)-- here is the new sqlorder by tc.create_time desc

After running, it is found that it still doesn't work, which proves that order by is after group by & having.

Later, I wonder if we can use order by to optimize the grouped results without having.

Having create_time = max (create_time)

Select tp.oid, tp.posts_name, tc.msg_content, tc.create_timefrom t_posts tp left join t_comment tc on tp.oid = tc.posts_idgroup by tp.oid order by tc.create_time desc

The result set error does not affect the grouping result. The repeated result set is still merged according to the rownum minimum grouping, and then sorted.

5. Ultimate modified version

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

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

It still doesn't work, but the subquery is sorted first.

After query (explain), it is found that the order by of the subquery has been optimized. The solution:

Using limit 99999 in subqueries

Use the where condition in the 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

The great task has been completed

Additional knowledge points:

The difference between mysql5.5 and mysql version 5.7: version 5.7 +. If you don't use limit, group by will optimize order by.

This is the end of the article on "how to use the MySql Group by function correctly". Thank you for reading! I believe you all have a certain understanding of the knowledge of "how to use the MySql Group by function correctly". If you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.