LAST_VALUE (Transact-SQL)

项目
03/07/2014

返回 SQL Server 2014 中有序值集中的最后一个值。

适用范围：SQL Server（SQL Server 2012 到当前版本）。

语法

LAST_VALUE ( [scalar_expression ) 
    OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

参数

scalar_expression
是要返回的值。 scalar_expression 可以是产生单个值的列、子查询或其他表达式。不允许使用其他分析函数。
OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
partition_by_clause 将由 FROM 子句生成的结果集划分为要应用函数的分区。如果未指定，则此函数将查询结果集的所有行视为单个组。

order_by_clause 在应用函数之前确定数据的顺序。 order_by_clause 是必需的。 rows_range_clause 通过指定起点和终点，限制分区中的行数。有关详细信息，请参阅 OVER 子句 (Transact-SQL)。

返回类型

与 scalar_expression 类型相同。

一般备注

LAST_VALUE 具有不确定性。有关详细信息，请参阅确定性函数和不确定性函数。

示例

A.对分区使用 LAST_VALUE

下面的示例会根据给定薪金（比率），返回每个部门中最后一个员工的雇佣日期。 PARTITION BY 子句按部门对员工分区，而 LAST_VALUE 函数独立应用于每个分区。在 OVER 子句中指定的 ORDER BY 子句确定对每个分区中的行应用 LAST_VALUE 函数的逻辑顺序。

USE AdventureWorks2012;
GO
SELECT Department, LastName, Rate, HireDate, 
    LAST_VALUE(HireDate) OVER (PARTITION BY Department ORDER BY Rate) AS LastValue
FROM HumanResources.vEmployeeDepartmentHistory AS edh
INNER JOIN HumanResources.EmployeePayHistory AS eph  
    ON eph.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = edh.BusinessEntityID
WHERE Department IN (N'Information Services',N'Document Control');

下面是结果集：

Department                  LastName                Rate         HireDate     LastValue
--------------------------- ----------------------- ------------ ----------   ----------
Document Control            Chai                    10.25        2003-02-23   2003-03-13
Document Control            Berge                   10.25        2003-03-13   2003-03-13
Document Control            Norred                  16.8269      2003-04-07   2003-01-17
Document Control            Kharatishvili           16.8269      2003-01-17   2003-01-17
Document Control            Arifin                  17.7885      2003-02-05   2003-02-05
Information Services        Berg                    27.4038      2003-03-20   2003-01-24
Information Services        Meyyappan               27.4038      2003-03-07   2003-01-24
Information Services        Bacon                   27.4038      2003-02-12   2003-01-24
Information Services        Bueno                   27.4038      2003-01-24   2003-01-24
Information Services        Sharma                  32.4519      2003-01-05   2003-03-27
Information Services        Connelly                32.4519      2003-03-27   2003-03-27
Information Services        Ajenstat                38.4615      2003-02-18   2003-02-23
Information Services        Wilson                  38.4615      2003-02-23   2003-02-23
Information Services        Conroy                  39.6635      2003-03-08   2003-03-08
Information Services        Trenary                 50.4808      2003-01-12   2003-01-12

B.在计算表达式中使用 FIRST_VALUE 和 LAST_VALUE

以下示例在计算表达式中使用 FIRST_VALUE 和 LAST_VALUE 函数，显示给定数量的员工当前季度分别与一年中第一季度和最后一季度之间的销售配额值差异。 FIRST_VALUE 函数返回该年度第一季度的销售配额值，并从当前季度的销售配额值中减去它。它在名为 DifferenceFromFirstQuarter 的派生列中返回。对于一年的第一季度，DifferenceFromFirstQuarter 列的值为 0。 LAST_VALUE 函数返回该年度最后一个季度的销售配额值，并从当前季度的销售配额值中减去它。它在名为 DifferenceFromLastQuarter 的派生列中返回。对于一年的最后一个季度，DifferenceFromLastQuarter 列的值为 0。

如下所示，对于要在 DifferenceFromLastQuarter 列中返回的非零值，子句“RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING”在此示例中是必需的。默认范围为“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”。在此示例中，使用默认范围（或不包括范围，导致使用默认范围）将导致在 DifferenceFromLastQuarter 列中返回零。有关详细信息，请参阅 OVER 子句 (Transact-SQL)。

USE AdventureWorks2012;
SELECT BusinessEntityID, DATEPART(QUARTER,QuotaDate)AS Quarter, YEAR(QuotaDate) AS SalesYear, 
    SalesQuota AS QuotaThisQuarter, 
    SalesQuota - FIRST_VALUE(SalesQuota) 
        OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate) 
              ORDER BY DATEPART(QUARTER,QuotaDate) ) AS DifferenceFromFirstQuarter, 
    SalesQuota - LAST_VALUE(SalesQuota) 
        OVER (PARTITION BY BusinessEntityID, YEAR(QuotaDate) 
              ORDER BY DATEPART(QUARTER,QuotaDate) 
              RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) AS DifferenceFromLastQuarter 
FROM Sales.SalesPersonQuotaHistory 
WHERE YEAR(QuotaDate) > 2005 
AND BusinessEntityID BETWEEN 274 AND 275 
ORDER BY BusinessEntityID, SalesYear, Quarter;