SQL Server 2005 - Row_Number() [英] SQL Server 2005 - Row_Number()

问题描述

我试图了解在查询中最外层选择上使用 DISITINCT 时使用 row_number() 函数按降序排序时看到的异常行为，如下所示:

I'm trying to understand the unusual behaviour seen when ordering results in a descending order using the row_number() function when using a DISITINCT on the outermost select in my query as below:

 SELECT   DISTINCT (ID), State_Id, Name_Of_Trip, Date_Of_Travel, Creation_Date, Locking_Id, Applicant_Name, Reference_Number, State_Name
    FROM (

    SELECT  app.ID, app.State_Id, app.Name_Of_Trip, app.Date_Of_Travel, app.Creation_Date, app.Locking_Id, app.Applicant_Name, app.Reference_Number, 
    State.Name AS State_Name, ROW_NUMBER() OVER(ORDER BY Reference_Number DESC) as rowNum  
    FROM Application_Leg AS app 
    INNER JOIN State AS state 
    ON app.State_Id = state.ID
    WHERE  (app.State_Id = 5 OR app.State_Id = 6 OR app.State_Id = 8)  AND app.Organisation_Id=12 
    AND Leg_Number IN
     (SELECT  DISTINCT Leg_Number 
     from Application_Leg as al
     INNER JOIN
     Organisation as org
     ON al.Organisation_Id = org.ID
     WHERE al.ID=app.ID AND org.Approval_Required=1 AND Mode_Of_Transport=1))
     as pagedApplications
    WHERE rowNum BETWEEN 0 AND (0 + 10)

当最外面的 DISTINCT 被取出时，降序是好的，但当它被包含时，结果不会按降序显示.

When the outermost DISTINCT is taken out then the descending order is fine but when it is included the results are not shown in descending order.

推荐答案

ORDER BY 在 ROW_NUMBER 子句中不保证结果集的顺序.

ORDER BY in ROW_NUMBER clause does not guarantee the order of the resultset.

ROW_NUMBER 通常在查询计划中使用排序，这会导致值预先排序.

ROW_NUMBER usually uses sorting in the query plan which results in the fact that the values come out presorted.

这是副作用，不应依赖.

This is a side effect and should not be relied upon.

DISTINCT 使用破坏排序的 Hash Match (Aggregate).

在查询末尾添加ORDER BY子句:

SELECT  DISTINCT (ID), State_Id, Name_Of_Trip, Date_Of_Travel, Creation_Date, Locking_Id, Applicant_Name, Reference_Number, State_Name
FROM    (
        SELECT  app.ID, app.State_Id, app.Name_Of_Trip, app.Date_Of_Travel,
                app.Creation_Date, app.Locking_Id, app.Applicant_Name, app.Reference_Number, 
                State.Name AS State_Name, ROW_NUMBER() OVER(ORDER BY Reference_Number DESC) as rowNum  
        FROM    Application_Leg AS app 
        INNER JOIN
                State AS state 
        ON      app.State_Id = state.ID
        WHERE   app.State_Id IN (5, 6, 8)
                AND app.Organisation_Id = 12 
                AND Leg_Number IN
                (
                SELECT  Leg_Number 
                FROM    Application_Leg as al
                INNER JOIN
                        Organisation as org
                ON      al.Organisation_Id = org.ID
                WHERE   al.ID = app.ID
                        AND org.Approval_Required = 1
                        AND Mode_Of_Transport = 1
                )
        ) AS pagedApplications
WHERE   rowNum BETWEEN 0 AND (0 + 10)
ORDER BY
        ReferenceNumber DESC

还要注意它不会返回10个不同的结果，它会返回第一个10个结果的DISTINCT.

Also note that it will not return 10 distinct results, it will return DISTINCT of the first 10 results.

如果你想要前者，使用这个:

If you want the former, use this:

SELECT  DISTINCT TOP 10 ID, State_Id, Name_Of_Trip, Date_Of_Travel, Creation_Date, Locking_Id, Applicant_Name, Reference_Number, State_Name
FROM    (
        SELECT  app.ID, app.State_Id, app.Name_Of_Trip, app.Date_Of_Travel,
                app.Creation_Date, app.Locking_Id, app.Applicant_Name, app.Reference_Number, 
                State.Name AS State_Name
        FROM    Application_Leg AS app 
        INNER JOIN
                State AS state 
        ON      app.State_Id = state.ID
        WHERE   app.State_Id IN (5, 6, 8)
                AND app.Organisation_Id = 12 
                AND EXISTS
                (
                SELECT  Leg_Number 
                FROM    Application_Leg AS al
                INNER JOIN
                        Organisation as org
                ON      al.Organisation_Id = org.ID
                WHERE   al.ID = app.ID
                        AND al.LegNumber = app.LegNumber
                        AND org.Approval_Required = 1
                        AND Mode_Of_Transport = 1
                )
        ) AS pagedApplications
ORDER BY
        ReferenceNumber DESC

这篇关于SQL Server 2005 - Row_Number()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！