Mysql filters SQL statements of two different fields

As we all know, the keywords commonly used in mysql to filter the same record are distinct, groupby, and so on. But how to filter the records with the same two different fields. The following is the problem requirement: A table named chat_history contains fields such as id, from_uid, and to_uid, where id is the auto-increment id and from_uid is the uid of the message sender, _

As we all know, in mysql, keywords such as distinct and group by are often used to filter the same records. But how can we filter records with the same fields. The following is the problem requirement: A table named chat_history contains fields such as id, from_uid, and to_uid, where id is the auto-increment id and from_uid is the uid of the message sender, _

As we all know, in mysql, keywords such as distinct and group by are often used to filter the same records. But how can we filter records with the same fields.

The following are the problem requirements:

The chat_history table contains fields such as id, from_uid, and to_uid. The id indicates the auto-incremental id, from_uid indicates the uid of the message sender, and to_uid indicates the uid of the receiver, now you need to get the latest contact for a uid.

The first idea is to perform the group by operation on from_uid and to_uid and then obtain the max (id) to filter out the value of from_uid or to_uid that is equal to the target uid.

According to this idea, we can obtain the following SQL statements:

select max(id) as mid,from_uid,to_uid from chat_history where from_uid="xxxxxx" or to_uid = "xxxxxx" group by from_uid, to_uid order by mid.

At this time, we can clearly find a problem by checking the results. Assume that two uid1 and uid2 communicate with each other, we get two data items through the preceding SQL statement: from_uid = uid1, to_uid = uid2 and from_uid = uid2, to_uid = uid1. Of course, this is not a problem. We have found the data. We can merge the results through a program to obtain the desired data.

In the case of few recent contacts, the above processing method is not suitable. However, when there are many contacts recently, we may think of paging the list page. However, the above practices obviously fail to meet our expectations. We want to integrate them into an SQL statement and use the limit keyword.

We hope to filter records such as from_uid = uid1, to_uid = uid2, from_uid = uid2, to_uid = uid1. The first reaction is that we can use the preceding SQL statement as a temporary table, the SQL statement is added to the outer layer for processing. Of course, this method is logically set up, but it is achieved at the expense of efficiency. The bloggers prefer to use the intermediate table.

By observing the above results, we can obtain such information: Can uid1 and uid2 be processed as a field, in mysql, the key sub-concat exists. This is to merge two fields, but the simple concat (from_uid, to_uid) still does not reach the expected results, but mysql provides logical operations, case operation.

You can change the preceding SQL statement as follows:

SELECT max( id ) AS mid, from_uid, to_uid,         CASE to_uid        WHEN 'xxx'THEN concat( to_uid, from_uid )ELSE concat( from_uid, to_uid )END AS to_from_uidFROM chat_historyWHERE to_uid = 'xxx' OR from_uid = 'xxx'GROUP BY to_from_uidORDER BY mid DESCLIMIT 0,20