Detailed explanation of the specific use of count(), group by, order by in MySQL

mysql中order by sortquery, asc ascending order, desc descending order, group by groupingquery, having can only be used In the group by clause and acting within the group, the having conditional clause can be directly followed by function expression. Query statements using the group by clause need to use the aggregation function.

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;

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;

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, among which The msg_data is actually the result of no sorting at all, which is the original order of the database. It should be a message inserted first, that is, the message with the oldest 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.

The above is the detailed content of Detailed explanation of the specific use of count(), group by, order by in MySQL. For more information, please follow other related articles on the PHP Chinese website!