如何从消息表中选择聊天？

Question

我有一个简单的消息表。

FROM|TO|DATETIME|MSG
----|--|--------|---
    |  |        |

给定一个用户，我想选择聊天。例如：

FROM|TO|DATETIME|MSG
----|--|--------|---
 A  |B |    1   | x
 B  |A |    2   | x
 B  |A |    3   | x
 A  |B |    4   | x
 C  |A |    8   | x

我想选择A的聊天(A为每个用户发送或接收的最后一次聊天)。

FROM|TO|DATETIME|MSG
----|--|--------|---
 C  |A |    8   | x
 A  |B |    4   | x

这就是我得到的

create procedure GetMessageHeaders
    @user varchar(30)
as
    with msg as
    (
        select M.transmitter,M.receiver
        from "MESSAGE" M
        where M.receiver=@user or M.transmitter=@user
        order by M.DATE desc
    )
    select *
    from msg

Answer 1

可能有一种更简单的方法，但下面的方法会产生所需的结果。我们首先生成我们开始的聊天和收到的聊天的组合记录集，然后计算出每个“其他”方的行号，然后我们只过滤出每个其他方的一行。

declare @Message table ([From] varchar(1), [To] varchar(1), [DateTime] int, msg varchar(1));

insert into @Message ([From], [To], [DateTime], msg)
  select 'A', 'B', 1, 'x'
  union all
  select 'B', 'A', 2, 'x'
  union all
  select 'B', 'A', 3, 'x'
  union all
  select 'A', 'B', 4, 'x'
  union all
  select 'C', 'A', 8, 'x'

select [From], [To], [DateTime], Msg
from (
  select *
    -- Calculate a row number per other party by time
    , row_number() over (partition by case when [Type] = 0 then [To] else [From] end order by [DateTime] desc) Row#
  from (
    -- Get chats we initiated
    select 0 [Type], [From], [To], [DateTime], Msg
    from @Message
    where [From] = 'A'
    union all
    -- Get chats we recevied
    select 1 [Type], [From], [To], [DateTime], Msg
    from @Message
    where [To] = 'A'
  ) X
) Y
where Row# = 1

Answer 2

我认为这样的方法是可行的。您需要每个分组的第一个实例按时间顺序排序。

    SELECT
        *
    FROM
    (
        SELECT
            *,
            LastInstanceReversed = ROW_NUMBER() OVER(PARTITION BY FROM,TO ORDER BY DATETIME DESC)
        FROM
            Message
    )AS X
    WHERE
        X.LastInstanceReversed=1