按計算列排序，在case運算式中具有別名

order by使用具有如下別名的計算列時，我的子句出現問題：

這order by作業沒有任何問題

declare @Mode int = 1
declare @Sort nvarchar(max) = 'engname'

select top 10 School.Id          as EntityId,
              School.EnglishName as EntityEnglishName,
              School.Name        as EntityNativeName,
              case
                  when @Mode = 0 then 0
                  when @Mode = 1 then 1
                  when @Mode = 2 then 2
                  end as ActiveStudents
from V_SchoolMinimized as School
Order By ActiveStudents

以下查詢有錯誤：

列名“ActiveStudents”無效

declare @Mode int = 1
declare @Sort nvarchar(max) = 'engname'

select top 10 School.Id          as EntityId,
       School.EnglishName as EntityEnglishName,
       School.Name        as EntityNativeName,
       case
           when @Mode = 0 then 0
           when @Mode = 1 then 1
           when @Mode = 2 then 2
           end as ActiveStudents
from V_SchoolMinimized as School
Order By
    case when @Sort is null then School.Id end,
    case when @Sort = 'engname' then ActiveStudents end

如圖所示，如何ActiveStudents在條件order by子句中使用？

uj5u.com熱心網友回復：

因此，雖然您可以在您的子句中使用計算列ORDER BY（但不能在其他子句中，例如GROUP BY），但您不能應用進一步的計算或條件 - 它必須完全按照創建的方式使用。

有很多方法可以解決這個問題。您使用哪種方法將歸結為以下幾種組合：

• 作為開發人員，您更清楚哪個選項
• 哪個選項性能更好
• 哪個選項更適合您現有的查詢

選項 1：重復邏輯

我不推薦這個選項，因為它違反了 DRY 原則，因此更難維護，更容易出錯。

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , case
          when @Mode = 0 then 0
          when @Mode = 1 then 1
          when @Mode = 2 then 2
      end as ActiveStudents
from V_SchoolMinimized as S
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then
        case
            when @Mode = 0 then 0
            when @Mode = 1 then 1
            when @Mode = 2 then 2
        end
    end;

其余選項是子查詢變體，其選擇歸結為作為開始提供的注釋。

選項 2：使用派生表子查詢

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , S.ActiveStudents
from (
    select *
        , case
              when @Mode = 0 then 0
              when @Mode = 1 then 1
              when @Mode = 2 then 2
          end as ActiveStudents
    from V_SchoolMinimized
) as S
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then S.ActiveStudents end;

選項 3：使用 CTE（公用表運算式）

with cte as (
    select *
        , case
              when @Mode = 0 then 0
              when @Mode = 1 then 1
              when @Mode = 2 then 2
          end as ActiveStudents
    from V_SchoolMinimized
)
select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , S.ActiveStudents
from cte
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then S.ActiveStudents end;

選項 4：使用CROSS APPLY

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , A.Students
from V_SchoolMinimized as S
cross apply (
    values (
        case
            when @Mode = 0 then 0
            when @Mode = 1 then 1
            when @Mode = 2 then 2
            end
    )
) as A (Students)
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then A.Students end;

注意：我建議保持你的表格別名簡潔明了，盡可能使用 1-2 個字符，偶爾使用 3 個字符。

標籤：sql sql服务器 tsql sql-order-by

上一篇：如何在多對多關系中找到所有相關記錄都滿足條件的記錄？

下一篇：如何動態獲取表中多行的總和