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

Editor to share with you how to solve the sorting problem of group by in Mysql, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

If there is a reply table for a post, posts (id, tid, subject, message, dateline)

Id is the auto-growth field, tid is the id (foreign key association) of the reply topic post, subject is the reply title, message is the reply content, and dateline is the reply time, which is indicated by the UNIX timestamp

Now ask to select the first ten latest responses from different topics.

SELECT * FROM posts GROUP BY tid LIMIT 10

Such a sql statement selects not the latest reply you want, but the earliest reply, which is actually the first reply record of a topic!

That is to say, GROUP BY statements are not sorted, so how do you get GROUP to be sorted in reverse order of dateline? Add the order by clause?

Look at the following:

SELECT * FROM posts GROUP BY tid ORDER BY dateline DESC LIMIT 10

The result selected by this statement is exactly the same as the above, but the results are arranged in reverse order, and each record selected is still the above record, because group by will be executed before order by, so there is no way to sort before group by, that is, before grouping, and some netizens will write the following sql statement:

SELECT * FROM posts GROUP BY tid DESC ORDER BY dateline DESC LIMIT 10

That is to say, add the descending order after the field tid of GROUP BY, so that you can get the final reply when grouping. The execution result of this statement will be exactly the same as above, and adding DESC and ASC here has no effect on the execution result! In fact, this is a wrong statement, because GROUP BY did not have sorting function before, the manual says, GROUP BY is sorted in a certain order, what is the order in the end? In fact, there is no order at all, because grouping according to tid, that is to say, grouping tid equally into a group, if you think of it this way, GROUP BY tid DESC can be thought of as grouping according to tid and arranging them in reverse order according to tid. Is that irrelevant? since they are grouped according to tid, of course, tid equals into one group, and at this time there is a P according to tid flashback or ascending order!

So some netizens invented the following sentence:

SELECT * FROM posts GROUP BY tid, dateline DESC ORDER BY dateline DESC LIMIT 10

I think that I can arrange it in reverse order according to dateline before grouping, in fact, this sentence does not play the role of grouping according to tid, the reason is still the above, adding desc or asc after the group by field is the wrong way to write, and this kind of writing is intended to group according to tid, and when grouping, it is arranged in reverse order according to dateline! In fact, this sentence is equivalent to the following: (remove the DESC after the GROUP BY field)

SELECT * FROM posts GROUP BY tid, dateline ORDER BY dateline DESC LIMIT 10

In other words, according to the joint grouping of tid and dateline, it can only be grouped into a group when recording that tid and dateline are equal at the same time, which is obviously impossible, because the dateline timeline is basically unique!

Someone wrote the following sentence:

SELECT *, max (dateline) as max_line FROM posts GROUP BY tid ORDER BY dateline DESC LIMIT 10

This sentence is true to choose the maximum release time, but you can compare that dateline and max_dateline are not equal! (there may be a considerable situation, that is, when there is only one target record for the group!)

And why is that? The reason is simple: this statement is equivalent to selecting the maximum release time for this group after group by! It didn't have any effect on the grouping! Because the SELECT clause is executed last!

Later, some netizens invented the following writing method!

SELECT *, max (dateline) as max_line FROM posts GROUP BY tid HAVING dateline=max (dateline)

ORDER BY dateline DESC LIMIT 10

The expected result of this statement is not the same as expected! Because you will find that a large number of records in the results of the grouping are gone! Why? Because HAVING is executed when grouping, that is to say, add a condition when grouping: the selected dateline is equal to the largest dateline in this group, and the execution result is the same as the following statement:

SELECT *, max (dateline) as max_line FROM posts GROUP BY tid HAVING count (*) = 1

ORDER BY dateline DESC LIMIT 10

Do you understand after reading this sql sentence?

Dateline=max (dateline) is valid only when there is only one record in the group. you can see why! Only one entry will be equal to the maximum release time of this group. (the default dateline is a value that does not repeat).

The reason is that group by has no sorting function, and all these sorting functions are just an illusion, so the dateline and max (dateline) you finally choose can never be equal, unless there is only one record in this group! When GROUP BY is grouped, it may be one by one to find that there is an equal tid, remove and keep the record found first, so the records found are always arranged in the default index order!

So after all that has been said, is there any way to get group by to implement the pre-grouping? Yes, sub-query!

The simplest:

SELECT * FROM (SELECT * FROM posts ORDER BY dateline DESC) GROUP BY tid ORDER BY dateline DESC LIMIT 10

There are also netizens who use self-join to achieve, this efficiency should be higher than the above sub-query efficiency, however, for simplicity and clarity, only this one, GROUP BY does not have sorting function, may be mysql mental retardation, maybe I have not found out

The above is all the contents of the article "how to solve the sorting problem of group by in Mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, 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.