SQL Server Row_number() OVER 与 GROUP BY

Question

我有“GetConversationPaged”sp，它从名为“user_messages_index”的表中选择分页数据，并在来自名为“social_user_messages”的表的对话中包含“lastmesage”。
我不确定它是否可以微调，但它可以在缺少一件事的情况下工作。我想通过使用 select MAX(messageId) 得到的 lastMessageId 对“Row_number”进行排序。

PS:我有消息索引和消息表，因为我希望人们向多个收件人发送相同的消息。我使用索引表而不是一遍又一遍地插入相同的消息

那么，如何按最新消息 ID 对结果进行排序？

这是我正在使用的表格和 sps

--索引表--

CREATE TABLE [dbo].[social_user_messages_index](
[senderId] [int] NOT NULL,
[recipientId] [int] NOT NULL,
[messageId] [int] NOT NULL,
[isRead] [bit] NOT NULL
 ) ON [PRIMARY]

--消息表--

CREATE TABLE [dbo].[social_user_messages](
[id] [int] IDENTITY(1,1) NOT NULL,
[message] [nvarchar](1000) NOT NULL,
[sendDate] [datetime] NOT NULL,
) ON [PRIMARY]

--sp--

ALTER PROCEDURE [dbo].[GetConversationPaged]
@userId int,
@isRead bit,
@PageNumber int,
@PageSize int
AS
BEGIN

    DECLARE @FirstRow  INT, @LastRow   INT, @RowCount  INT, @PageCount INT 
    --- find recordcount and pages 
    SELECT @RowCount = COUNT(1) OVER(), 
           @PageCount = COUNT(*) OVER() / @PageSize 
            FROM   social_user_mesages_index s
            WHERE  s.recipientId = @userId and s.isRead=@isRead
            GROUP BY senderId 
    --- calculate pages
    IF @RowCount % @PageSize != 0 SET @PageCount = @PageCount + 1 
    IF @PageNumber < 1  SET @PageNumber = 1 
    IF @PageNumber > @PageCount  SET @PageNumber = @PageCount 
    --- select paging data
    SELECT currentpage = @PageNumber, totalpages = @PageCount, totalrows = @RowCount 
    SET @FirstRow = ( @PageNumber - 1 ) * @PageSize + 1;
    SET @LastRow = ( @PageNumber - 1 ) * @PageSize + @PageSize; 

    --- select records     
    WITH mytable 
         AS (

            SELECT  Row_number() OVER (ORDER BY (SELECT 1)) AS rownumber,
                    (Select name from domains_users d where d.id=s.senderId) as senderName,
                    (select MAX(messageId)) as lastMessageId,
                    (select m.[message] from social_user_messages m where m.id = (select MAX(messageId))) as [message]
                    --,(select m.sendDate from social_user_messages m where m.id = (select MAX(messageId))) as lastMessageDate
                    ,senderId   
            FROM   social_user_mesages_index s
            WHERE  s.recipientId = @userId and s.isRead=@isRead
            GROUP BY senderId 

         ) 

    SELECT * 
    FROM   mytable 
    WHERE  rownumber BETWEEN @FirstRow AND @LastRow 
    ORDER  BY rownumber ASC; 
END

Answer 1

在您的 CTE 中，您的 social_user_messages_index 拼写错误。我还删除了 Group BY SenderId你有几个地方，并更新了 CTE。如果这不符合您的希望，请告诉我，我可以对其进行调整:

ALTER PROCEDURE [dbo].[GetConversationPaged]
@userId int,
@isRead bit,
@PageNumber int,
@PageSize int
AS
BEGIN

   DECLARE @FirstRow  INT, @LastRow   INT, @RowCount  INT, @PageCount INT 
   --- find recordcount and pages 
   SELECT @RowCount = COUNT(1) OVER()
      , @PageCount = COUNT(*) OVER() / @PageSize 
   FROM   dbo.social_user_messages_index AS s
   WHERE  s.recipientId = @userId and s.isRead=@isRead

   --- calculate pages
   IF @RowCount % @PageSize != 0 SET @PageCount = @PageCount + 1 
   IF @PageNumber < 1 SET @PageNumber = 1 
   IF @PageNumber > @PageCount SET @PageNumber = @PageCount 

   --- select paging data
   SELECT currentpage = @PageNumber
      , totalpages = @PageCount
      , totalrows = @RowCount;
   SET @FirstRow = ( @PageNumber - 1 ) * @PageSize + 1;
   SET @LastRow = ( @PageNumber - 1 ) * @PageSize + @PageSize; 

   --- select records     
   WITH mytable AS
   (
      SELECT Row_number() OVER (ORDER BY MessageId DESC) AS rownumber
         , du.name as senderName
         , m.Message
         , senderId
      FROM social_user_messages_index AS s
      INNER JOIN dbo.domains_users AS du ON s.SenderId = du.id
      INNER JOIN dbo.social_user_messages AS m ON s.messageId = m.id
      WHERE s.recipientId = @userId
         AND s.isRead=@isRead
   )
   SELECT * 
   FROM   mytable 
   WHERE  rownumber BETWEEN @FirstRow AND @LastRow 
   ORDER  BY rownumber ASC;
END