OVER 子句.如何按 CASE 子句中的多列排序? [英] OVER clause. How to order by multiple columns within a CASE clause?

问题描述

以下 SQL 代码是较大的 SQL 语句示例.我的问题是如何在 SELECT ROW_NUMBER() 中执行 CASE 多列 ORDER BY，类似于下面的注释行.提供的代码有效，但我需要按两列排序.

The following SQL code is a sample of larger SQL statement. My question is how can I do CASE multiple column ORDER BY in the SELECT ROW_NUMBER(), similar to the one below comment line. The presented code works, but I need to order by two columns.

我使用的是 MSSQL 2008

I am using MSSQL 2008

SELECT TOP(50)
    ROW_NUMBER() OVER(ORDER BY CASE WHEN @OrderBy = 'Total' THEN SUM(TotalViews) ELSE SUM(LastMonthViews) END DESC) AS Position
    ,SUM(Albums.TotalViews) AS TotalViews
    ,SUM(Albums.LastMonthViews) AS LastMonthViews
FROM Albums

--The code to be implemented in the SELECT ROW_NUMBER()
ORDER BY SUM(LastMonthViews) DESC, SUM(TotalViews) DESC

推荐答案

你可以重复row_number()分区子句中的case语句:

You can repeat the case statement in the row_number() partitioning clause:

SELECT TOP(50)
        ROW_NUMBER() OVER (ORDER BY (CASE WHEN @OrderBy = 'Total' THEN SUM(LastMonthViews) ELSE SUM(LastMonthViews) END) DESC,
                                    (CASE WHEN @OrderBy = 'Total' THEN SUM(TotalViews) ELSE SUM(LastMonthViews) END) DESC
                          ) AS Position
    ,SUM(Albums.TotalViews) AS TotalViews
    ,SUM(Albums.LastMonthViews) AS LastMonthViews
FROM Albums;

这篇关于OVER 子句.如何按 CASE 子句中的多列排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！