SQL Server中的GROUP BY在复杂查询中

亚当·摩尔

我需要将其分组T.TopicID以仅接收最后的结果。

无论我尝试什么，都会收到类似分组或汇总等中包含的其他T.项目错误的错误。

ALTER PROCEDURE [dbo].[SPGetFollowingTopics]
    @id int = null
    ,@UserGroupId int = null
    ,@lastvisit DateTime = null
AS
    SELECT *
    FROM
       (SELECT
           ROW_NUMBER() OVER (ORDER BY TopicOrder DESC,
                                    (CASE 
                                        WHEN M.MessageCreationDate > T.TopicCreationDate 
                                          THEN M.MessageCreationDate
                                          ELSE T.TopicCreationDate
                                      END) DESC) AS RowNumber, 
           T.TopicId, T.TopicTitle, T.TopicShortName,  
           T.TopicDescription, T.TopicCreationDate, T.TopicViews, 
           T.TopicReplies, T.UserId, T.TopicTags, T.TopicIsClose, 
           T.TopicOrder, T.LastMessageId, U.UserName, 
           M.MessageCreationDate, T.ReadAccessGroupId, 
           T.PostAccessGroupId, TF.userid AS Expr1, U.UserGroupId, 
           U.UserPhoto, U.UserFullName, M.UserId AS MessageUserId,
           MU.UserName AS MessageUserName
        FROM            
           Topics AS T 
        LEFT OUTER JOIN
           Messages AS M ON M.TopicId = T.TopicId AND M.Active = 1 AND M.MessageCreationDate < @lastvisit
        INNER JOIN 
           topicfollows AS TF ON T.TopicId = TF.topicid    
        INNER JOIN
           Users AS U ON U.UserId = T.UserId
        LEFT JOIN 
           Users MU ON MU.UserId = M.UserId
        WHERE
           (TF.userid = @id) 
       ) T

保罗·麦克斯韦

（我认为）尚不清楚要求是什么，但我认为您正在寻求：

“最新消息”
每个主题
对于给定的用户

在这种情况下，ROW_NUMBER（）是一个不错的选择，但我相信您需要对ROW_NUMBER进行分区和排序。

SELECT
      *
FROM (
      SELECT
            ROW_NUMBER() OVER (PARTITION BY TF.userid, T.TopicId 
                               ORDER BY
                                    (CASE
                                          WHEN M.MessageCreationDate > T.TopicCreationDate THEN M.MessageCreationDate
                                          ELSE T.TopicCreationDate
                                    END) DESC) AS ROWNUMBER
          , T.TopicId, T.TopicTitle, T.TopicShortName, T.TopicDescription
          , T.TopicCreationDate, T.TopicViews, T.TopicReplies, T.UserId
          , T.TopicTags, T.TopicIsClose, T.TopicOrder, T.LastMessageId
          , U.UserName, M.MessageCreationDate, T.ReadAccessGroupId
          , T.PostAccessGroupId, TF.userid AS EXPR1
          , U.UserGroupId, U.UserPhoto, U.UserFullName
          , M.UserId AS MESSAGEUSERID, MU.UserName AS MESSAGEUSERNAME
      FROM Topics AS T
      LEFT OUTER JOIN Messages AS M ON M.TopicId = T.TopicId
                  AND M.Active = 1
                  AND M.MessageCreationDate < @lastvisit
      INNER JOIN topicfollows AS TF ON T.TopicId = TF.topicid
      INNER JOIN Users AS U ON U.UserId = T.UserId
      LEFT JOIN Users MU ON MU.UserId = M.UserId
      WHERE (TF.userid = @id)
) T
WHERE ROWNUMBER = 1