sql - SQL：根据时间戳和聊天伙伴分组消息

Question

嘿，我目前正在聊天，我想显示聊天伙伴的用户列表（就像在 WhatsApp 或 Telegram 上一样），其中包含聊天伙伴名称、图片、未读消息的数量以及基于时间戳的最新消息。我正在为（Postgres）SQL 查询而苦苦挣扎。

那是表消息：

+-------+---------+-------+-------------------------------+---------+---------------+
| me_id | me_from | me_to |         me_timestamp          | me_read |  me_content   |
+-------+---------+-------+-------------------------------+---------+---------------+
|     1 |       1 |     2 | 2019-01-04 02:50:53.931637+00 | true    | hey           |
|     2 |       1 |     2 | 2019-01-04 02:57:53.931637+00 | true    | how are you?  |
|     3 |       1 |     2 | 2019-01-04 03:10:53.931637+00 | true    | you there?    |
|     4 |       1 |     3 | 2019-01-05 01:57:53.931637+00 | true    | good night    |
|     5 |       2 |     1 | 2019-01-05 02:10:53.931637+00 | false   | I am here     |
|     6 |       3 |     2 | 2019-01-05 03:57:53.931637+00 | false   | see you later |
|     7 |       3 |     1 | 2019-01-05 04:57:53.931637+00 | false   | okay          |
+-------+---------+-------+-------------------------------+---------+---------------+

成员表：

+------+------------+-------------+
| m_id | m_nickname |  m_picture  |
+------+------------+-------------+
|    1 | John       | selfie.jpg  |
|    2 | CocoGirly  | selfie2.jpg |
|    3 | MileyStar  | selfie3.jpg |
|    3 | JackX      | selfie4.jpg |
+------+------------+-------------+

我希望它看起来像这样（图片中不相关的消息内容和名称）：

这就是我到目前为止所拥有的：

SELECT m_id,
       m_nickName,
       m_picture
FROM member
WHERE m_id IN
    ( SELECT me_to AS chatPartner
     FROM
       ( SELECT me_to
        FROM message
        WHERE me_from = 1
        GROUP BY me_to
        UNION SELECT me_from
        FROM message
        WHERE me_to = 1
        GROUP BY me_from) A)

非常感谢您！

标签： sqlpostgresqlselect

Answer 1

对成员表多次使用连接

select m.m_nickname as my_name,m.m_picture as my_picture,
       m1.m_nickname as from_name,m1.m_picture as from_pic,
       m2.m_nickname as partner_name ,m2.m_picture  as partner_pic
             from message msg left join  member m on msg.me_id=m.m_id
              left join member m1 on msg.me_from =m1.m_id
               left join member m2  on msg.me_to=m2.m_id