MySql，如何从不同用户获取最后的消息

Question

My table looks like this

id INT
from9 VARCHAR 12
to9 VARCHAR 12
message TEXT
datetime9 DATETIME

from9 and to9 are users' ids. Obviously if Susan (ID:susan) sends a message to Jack (ID:jack), row in MySql table will look like:

55 (Auto-increment INT)
susan
jack
Hello Jack how are you doing?
2016-11-14 22:22:22

If Jack sends message back, from9 and to9 will be invese. As you can see I am saving one message for both parties (this is fine). I would like to list all "last" messages either send to me OR sent by me and I am having problems building such query. I was thinking to do workaround and have more queries: 1) get msgs sent to me 2) get msgs sent by me 3) put all those msgs in a set and do final query where I read those IDs.

Is it possible to build one query for this?

Answer 1

I Hope you will get answer from this query

SELECT max(datetime),msg FROM `message` WHERE from9="a" or to9="a" 

Here "datetime" is my date field name in db and am generating query with a message which is sent or receive by the user "a" last time