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

This article is about how to use count (), group by and order by in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The usage of count (), group by and order by in mysql, order by sorting query, asc ascending order, desc descending order in mysql, group by grouping query, having can only be used in the group by clause, acting in the group, and the having conditional clause can be directly followed by the function expression. Query statements that use the group by clause require aggregate functions.

Recently, I encountered a problem when I was doing IM. I used these three keywords at the same time. It is to query the details of a person's offline messages. The details returned by our server to the client include three contents. The first one is required to list which people or groups have sent you messages during the offline period. The second one is one of them or how many offline messages have been sent in groups. The third one shows the latest one. Obviously, group by grouped which people or groups sent you offline messages, count () got the number of offline messages, order by time to sort out the latest messages.

Select count (1) as cnt, msg_data from t_im_chat_offline_msg where to_company_id =? And to_user_id =? Order by create_time desc group by from_company_id, from_user_id

;. And then sure enough, group by and order by search together and report an error, we can use nested subqueries.

Select count (1) as cnt, msg_data from (select * from t_im_chat_offline_msg where to_company_id =? And to_user_id =? Order by create_time desc) as temp_table group by from_company_id, from_user_id

We can group the sorted result sets and calculate the number. There is also a sinkhole, which I accidentally avoided. In fact, count () will invalidate order by sorting, for example:

Select count (1) as cnt, msg_data from t_im_chat_offline_msg where to_company_id =? And to_user_id =? Order by create_time desc

The last record of this statement, in which the msg_data is actually the result of no sorting at all, that is, the original order of the database, should be a message inserted first, that is, the old message. To avoid this problem, the nested subquery sorts here and then lets it go to count (). I unintentionally dodged it, but luckily I repeatedly changed the sentence and tested it before I found it.

Thank you for reading! This is the end of the article on "how to use count (), group by and order by in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.