admin

SQL Server:从分组依据中选择最后一个结果

sql

我正在尝试从2个相似的结果(最旧的结果)中检索最后一个结果

FromUser | ToUser | Message | Date
User1    | User2  | hi      | 01/01/2013 20:00
User1    | User2  | hi later| 01/01/2013 21:00
User5    | User2  | hi      | 01/01/2013 20:01
User5    | User2  | hi later| 01/01/2013 21:01

SELECT        
    CM.FromUser, CM.ToUser, CM.Message, CM.Date, 
    U.UserId, U.UserFullName, U.UserName, U.UserPhoto
FROM
    ConversationMessages AS CM 
INNER JOIN
    Users AS U ON U.UserName = CM.FromUser
WHERE
    CM.ToUser = @user
ORDER BY 
    CM.Date DESC

它应先列出用户5嗨,然后再列出用户1嗨,然后再列出(用户5在日期上较新)。只有最后一行基本上按FromUser日期分组,然后按日期排序。我对sql没用,在这里尝试了许多建议,但没有任何工作


阅读 69

收藏
2021-07-01

共1个答案

admin

您想为此使用窗口函数,而不是group by

select FromUser, ToUser, Message, [Date], UserId, UserFullName, UserName, UserPhoto
from (SELECT CM.FromUser, CM.ToUser, CM.Message, CM.Date, U.UserId,
             U.UserFullName, U.UserName, U.UserPhoto,
             row_number() over (partition by CM.FromUser, CM.ToUser order by [Date] desc) as seqnum
      FROM ConversationMessages CM INNER JOIN
           Users U
           ON U.UserName = CM.FromUser
      WHERE  CM.ToUser = @user
     ) s
WHERE seqnum = 1
ORDER BY CM.Date DESC ;
2021-07-01