tsql - Row_Number() 和 T-SQL View

Question

我正在尝试为 T-SQL 中的 View 实现分页:

with TH_VW_UserFollowing  as
(
Select  FollowerID, 
        FollowingID, 
        FollowingFullName, 
        FollowingImage, 
        FollowingUserName,
        dbo.GetUserFollowers(FollowingID) AS Followers,
        ROW_NUMBER() OVER (order by dbo.GetUserFollowers(FollowingID) DESC ) AS 'RowNumber'   
from dbo.TH_VW_UserFollowing 
where FollowerID = @UserID 
)
Select  FollowerID, 
        FollowingID, 
        FollowingFullName, 
        FollowingImage, 
        FollowingUserName, Followers
from dbo.TH_VW_UserFollowing
Where RowNumber BETWEEN @startIdx AND @endIdx

我遇到这些错误是有原因的:

Msg 207, Level 16, State 1, Procedure GetUserUsersFollowing, Line 36
Invalid column name 'RowNumber'. Msg 207, Level 16, State 1, Procedure
GetUserUsersFollowing, Line 36 Invalid column name 'RowNumber'. Msg
207, Level 16, State 1, Procedure GetUserUsersFollowing, Line 34
Invalid column name 'Followers'.

我对表格使用了相同的代码，但我不知道这里发生了什么。有些东西不见了......

谢谢。

Answer 1

您是从表中选择的，而不是您在上面定义的 CTE。您应该在最后的 SELECT 中执行“FROM TH_VW_UserFollowing”。我还建议为您的 CTE 取一个与您的表不同的名称。