Share how to use count(), group by, order by in mysql

This article mainly shares with you how to use count(), group by, and order by in mysql. In mysql, order by sorting query, asc ascending order, desc descending order, group by group query, and having can only be used in the group by clause. Acting within a group, the having conditional clause can be directly followed by a function expression. Query statements using the group by clause require the use of aggregate functions.

I recently encountered a problem when doing IM, and these three keywords were used at the same time. It is to query the offline message details of a person. The details returned by our server to the client include three contents. The first one requires listing which people or groups have sent you messages during the offline period. The second one is this How many offline messages has been sent by a certain person or group, and the third one takes out the latest one and displays it. Obviously, group by groups which people or groups have sent you offline messages, count() gets the number of offline messages, and order by time sorts 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;

Then as expected, an error will be reported when group by and order by are retrieved together. 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;<br>

We can group the sorted result set and calculate the quantity. There is another hidden pit here, which I accidentally avoided. In fact, count() will cause order by sorting to be invalid. 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 obtained by this statement, the msg_data is actually the result of no sorting at all, which is the original order of the database , it should be the message inserted first, that is, the message with an older time. In order to avoid this problem, the nested subquery is sorted here first and then used to count() to avoid it. I escaped unintentionally, but luckily I found out after repeatedly changing the sentence and testing it.

Related recommendations:

10 recommended articles about count()

##Using Group By to group multiple fields in MySql Method

Detailed explanation of Order By multi-field sorting rules in MySQL

The above is the detailed content of Share how to use count(), group by, order by in mysql. For more information, please follow other related articles on the PHP Chinese website!