这个SQL应该怎么写？

[ruoxuefengying]

要实现的功能：查询与我对话的所有人的最新一条记录（注意理解）
比如，我跟A说过很多句话、A也跟我说过很多句话、我跟B说过很多句话、B也跟我说过很多句话，如何查询出我跟A的最新一条对话以及我跟B的最新一条对话（无论我是对话的sender还是receiver）

数据库表如下：



比如我的user_id为3，查询与user_id_sender或user_id_receiver为3对话过的所有用户的分别的最新一条对话记录
跪求大神帮助~

[江南小鱼]

select *
from t_user_letter t
where time = (select MAX(time) from t_user_letter
where (user_id_sender = t.user_id_sender and user_id_receiver = t.user_id_receiver)
or (user_id_sender = t.user_id_receiver and user_id_receiver = t.user_id_sender)) 

[kevinjay567]

进来学习学习

[Tiger_Zhao]

DECLARE @my_id int
SET @my_id = 3

;WITH t AS ( -- 我对话的所有记录
    SELECT user_id_sender AS contacter_id, *
      FROM t_user_letter
     WHERE user_id_receiver = @my_id
    UNION ALL
    SELECT user_id_receiver AS contacter_id, *
      FROM t_user_letter
     WHERE user_id_sender = @my_id
)
,t1 AS ( -- 我所有的联系人
    SELECT DISTINCT contacter_id
      FROM T
)
        SELECT t.*
          FROM t1
   CROSS APPLY ( -- 每个联系人的最新记录
                    SELECT TOP 1 *
                      FROM t
                     WHERE contacter_id = t1.contacter_id
                  ORDER BY time DESC
               ) s

[ruoxuefengying]

引用 1 楼 lovelj2012 的回复:

select *
from t_user_letter t
where time = (select MAX(time) from t_user_letter
where (user_id_sender = t.user_id_sender and user_id_receiver = t.user_id_receiver)
or (user_id_sender = t.user_id_receiver and user_id_receiver = t.user_id_sender)) 

亲测可用，非常感谢！

[ruoxuefengying]

多谢2楼的回答了，@Tiger_Zhao ，学习了~