LAST_VALUE (Transact-SQL)

傳回 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。

此範例需要子句 “RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING”，才能在 DifferenceFromLastQuarter 資料行中傳回非零值，如下所示。預設範圍為 “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;