SQL Server存储过程ORDER BY中CASE使用别名报错的解决方法

我使用SQL Server 2022编写了如下存储过程：

Select 
    A.Id, A.Title, A.BriefText, 
    I.ArticleView, U.DisplayName AS Author, 
    U.Avatar,
    (SELECT COUNT(*) FROM Articles_Like WHERE ArticleId = A.Id) AS Likes,
    (SELECT COUNT(*) FROM Articles_Comment WHERE ArticleId = A.Id) AS Comments,
    CASE 
        WHEN EXISTS (SELECT Id FROM Articles_Like 
                     WHERE ArticleId = A.Id AND UserId = @UserId) 
            THEN 1 
            ELSE 0 
    END AS IsLiked,
    CASE 
        WHEN EXISTS (SELECT Id FROM Articles_Bookmark 
                     WHERE ArticleId = A.Id AND UserId = @UserId)
            THEN 1 
            ELSE 0 
    END AS IsBookmarked
FROM 
    Articles A
LEFT OUTER JOIN 
    Articles_Info I ON I.ArticleId = A.Id 
LEFT OUTER JOIN 
    Users_Info U ON U.UserId = A.UserId
GROUP BY 
    A.Title, A.BriefText, A.Id, I.ArticleView, 
    U.DisplayName, U.Avatar, U.UserId 
ORDER BY 
    CASE WHEN @orderby = 1 THEN A.Id END DESC,
    CASE WHEN @orderby = 2 THEN I.ArticleView END DESC,
    CASE WHEN @orderby = 3 THEN Likes END DESC

在ORDER BY的最后一行CASE WHEN @orderby = 3 THEN Likes END DESC处，出现「Invalid column name」错误——SQL Server不允许在ORDER BY的CASE语句中直接引用SELECT子句定义的别名Likes。虽然将代码改为重复子查询CASE WHEN @orderby=3 THEN (SELECT COUNT(*) FROM Articles_Like WHERE ArticleId=A.Id) END DESC可正常运行，但不想重复编写这段子查询代码，请问该如何正确解决此问题？

下面提供三种可行的方案，避免重复子查询同时解决排序问题：

方案1：使用CTE预计算所有字段 #

通过公共表表达式（CTE）先计算出包含Likes在内的所有字段，后续查询直接引用别名，ORDER BY即可正常使用：

WITH ArticleStats AS (
    SELECT 
        A.Id, A.Title, A.BriefText, 
        I.ArticleView, U.DisplayName AS Author, 
        U.Avatar,
        (SELECT COUNT(*) FROM Articles_Like WHERE ArticleId = A.Id) AS Likes,
        (SELECT COUNT(*) FROM Articles_Comment WHERE ArticleId = A.Id) AS Comments,
        CASE 
            WHEN EXISTS (SELECT Id FROM Articles_Like WHERE ArticleId = A.Id AND UserId = @UserId) 
                THEN 1 
                ELSE 0 
        END AS IsLiked,
        CASE 
            WHEN EXISTS (SELECT Id FROM Articles_Bookmark WHERE ArticleId = A.Id AND UserId = @UserId)
                THEN 1 
                ELSE 0 
        END AS IsBookmarked
    FROM 
        Articles A
    LEFT OUTER JOIN 
        Articles_Info I ON I.ArticleId = A.Id 
    LEFT OUTER JOIN 
        Users_Info U ON U.UserId = A.UserId
    GROUP BY 
        A.Title, A.BriefText, A.Id, I.ArticleView, 
        U.DisplayName, U.Avatar, U.UserId 
)
SELECT *
FROM ArticleStats
ORDER BY 
    CASE WHEN @orderby = 1 THEN Id END DESC,
    CASE WHEN @orderby = 2 THEN ArticleView END DESC,
    CASE WHEN @orderby = 3 THEN Likes END DESC;

方案2：使用子查询包裹主查询 #

原理和CTE类似，将主查询作为子查询，外层查询直接使用子查询返回的别名进行排序：

SELECT *
FROM (
    Select 
        A.Id, A.Title, A.BriefText, 
        I.ArticleView, U.DisplayName AS Author, 
        U.Avatar,
        (SELECT COUNT(*) FROM Articles_Like WHERE ArticleId = A.Id) AS Likes,
        (SELECT COUNT(*) FROM Articles_Comment WHERE ArticleId = A.Id) AS Comments,
        CASE 
            WHEN EXISTS (SELECT Id FROM Articles_Like WHERE ArticleId = A.Id AND UserId = @UserId) 
                THEN 1 
                ELSE 0 
        END AS IsLiked,
        CASE 
            WHEN EXISTS (SELECT Id FROM Articles_Bookmark WHERE ArticleId = A.Id AND UserId = @UserId)
                THEN 1 
                ELSE 0 
        END AS IsBookmarked
    FROM 
        Articles A
    LEFT OUTER JOIN 
        Articles_Info I ON I.ArticleId = A.Id 
    LEFT OUTER JOIN 
        Users_Info U ON U.UserId = A.UserId
    GROUP BY 
        A.Title, A.BriefText, A.Id, I.ArticleView, 
        U.DisplayName, U.Avatar, U.UserId 
) AS ArticleData
ORDER BY 
    CASE WHEN @orderby = 1 THEN Id END DESC,
    CASE WHEN @orderby = 2 THEN ArticleView END DESC,
    CASE WHEN @orderby = 3 THEN Likes END DESC;

方案3：提前聚合关联，优化性能 #

将点赞数、评论数提前聚合后通过LEFT JOIN关联到主查询，既避免了相关子查询的重复，还能提升查询性能（尤其数据量大时），同时ORDER BY可直接使用关联后的聚合字段：

Select 
    A.Id, A.Title, A.BriefText, 
    I.ArticleView, U.DisplayName AS Author, 
    U.Avatar,
    COALESCE(L.LikeCount, 0) AS Likes,
    COALESCE(C.CommentCount, 0) AS Comments,
    CASE 
        WHEN EXISTS (SELECT Id FROM Articles_Like WHERE ArticleId = A.Id AND UserId = @UserId) 
            THEN 1 
            ELSE 0 
    END AS IsLiked,
    CASE 
        WHEN EXISTS (SELECT Id FROM Articles_Bookmark WHERE ArticleId = A.Id AND UserId = @UserId)
            THEN 1 
            ELSE 0 
    END AS IsBookmarked
FROM 
    Articles A
LEFT OUTER JOIN 
    Articles_Info I ON I.ArticleId = A.Id 
LEFT OUTER JOIN 
    Users_Info U ON U.UserId = A.UserId
LEFT JOIN (
    SELECT ArticleId, COUNT(*) AS LikeCount
    FROM Articles_Like
    GROUP BY ArticleId
) L ON L.ArticleId = A.Id
LEFT JOIN (
    SELECT ArticleId, COUNT(*) AS CommentCount
    FROM Articles_Comment
    GROUP BY ArticleId
) C ON C.ArticleId = A.Id
GROUP BY 
    A.Title, A.BriefText, A.Id, I.ArticleView, 
    U.DisplayName, U.Avatar, U.UserId, L.LikeCount, C.CommentCount 
ORDER BY 
    CASE WHEN @orderby = 1 THEN A.Id END DESC,
    CASE WHEN @orderby = 2 THEN I.ArticleView END DESC,
    CASE WHEN @orderby = 3 THEN L.LikeCount END DESC;

内容的提问来源于stack exchange，提问作者Reza245