CTE中的自定義排序順序

我需要在 CTE 中獲得自定義排序順序，但錯誤顯示

“--ORDER BY 子句在視圖、行內函式、派生表、子查詢和公用表運算式中無效，除非還指定了 TOP、OFFSET 或 FOR XML。”

在 CTE 中獲得自定義訂單的更好方法是什么？

WITH
ctedivisiondesc
as
(
SELECT * FROM (
  SELECT --TOP 1 --[APPID]
       DH1.[ID_NUM]
      --,[SEQ_NUM_2]
      --,[CUR_DEGREE]
      --,[NON_DEGREE_SEEKING]
      ,DH1.[DIV_CDE]
      ,DDF.DEGREE_DESC 'DivisionDesc'
      --,[DEGR_CDE]
      --,[PRT_DEGR_ON_TRANSC]
      --,[ACAD_DEGR_CDE]
      ,[DTE_DEGR_CONFERRED]
      --,MAX([DTE_DEGR_CONFERRED]) AS Date_degree_conferred
      ,ROW_NUMBER() OVER (
                         PARTITION BY [ID_NUM] 
                         ORDER BY [DTE_DEGR_CONFERRED] DESC --Getting last degree
                   ) AS [ROW NUMBER]

  FROM [TmsePrd].[dbo].[DEGREE_HISTORY] As DH1
      inner join [TmsePrd].[dbo].[DEGREE_DEFINITION] AS DDF
      on DH1.[DEGR_CDE] = DDF.[DEGREE]
      --ORDER BY 
                --DIV_CDE                       Level
                --CE    Continuing Education    
                --CT    Certificate             1
                --DC    Doctor of Chiropractic  4
                --GR    Graduate                3
                --PD    Pending Division    
                --UG    Undegraduate            2

        --The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.
        ORDER BY CASE 
                   WHEN DDF.DEGREE_DESC = 'Certificate' THEN 1
                   WHEN DDF.DEGREE_DESC = 'Undegraduate' THEN 2
                   WHEN DDF.DEGREE_DESC = 'Graduate' THEN 3
                   WHEN DDF.DEGREE_DESC = 'Doctor of Chiropractic' THEN 4
                   ELSE 5
                 END 

  ) AS t
  WHERE [ROW NUMBER] <= 1
)
SELECT * FROM ctedivisiondesc

uj5u.com熱心網友回復：

您需要對外部查詢進行排序。

不允許對子查詢進行排序，因為它沒有意義，考慮這個簡單的例子：

WITH CTE AS
(    SELECT  ID
     FROM (VALUES (1), (2)) AS t (ID)
     ORDER BY ID DESC
 )
 SELECT *
 FROM CTE
 ORDER BY ID ASC;

外部查詢的排序覆寫了內部查詢的排序，從而浪費了時間。

它也不僅僅是外部查詢的顯式排序，在更復雜的場景中，SQL Server 可能會以任何它希望啟用合并連接或分組等的方式對子查詢進行排序。因此，保證順序或結果的唯一方法是排序外部查詢如你所愿。

由于您可能沒有外部查詢中需要的所有資料，因此您可能需要在 CTE 內創建另一個列以用于排序。例如

WITH ctedivisiondesc AS
(
    SELECT  *
    FROM    (   SELECT  DH1.ID_NUM,
                        DH1.DIV_CDE,
                        DDF.DEGREE_DESC AS DivisionDesc,
                        DTE_DEGR_CONFERRED,
                        ROW_NUMBER() OVER (PARTITION BY ID_NUM ORDER BY DTE_DEGR_CONFERRED DESC) AS [ROW NUMBER],
                        CASE 
                           WHEN DDF.DEGREE_DESC = 'Certificate' THEN 1
                           WHEN DDF.DEGREE_DESC = 'Undegraduate' THEN 2
                           WHEN DDF.DEGREE_DESC = 'Graduate' THEN 3
                           WHEN DDF.DEGREE_DESC = 'Doctor of Chiropractic' THEN 4
                           ELSE 5
                         END AS SortOrder
                FROM    TmsePrd.dbo.DEGREE_HISTORY AS DH1
                        INNER JOIN TmsePrd.dbo.DEGREE_DEFINITION AS DDF
                            ON DH1.DEGR_CDE = DDF.DEGREE

            ) AS t
    WHERE   t.[ROW NUMBER] <= 1
)
SELECT  ID_NUM,
        DIV_CDE,
        DivisionDesc,
        DTE_DEGR_CONFERRED
FROM    ctedivisiondesc
ORDER BY SortOrder;

標籤：sql服务器 tsql

上一篇：游標將值存盤在變數中但未在另一個表SqlServer中更新

下一篇：處理sql-無法從變數中選擇