LAST_VALUE (Transact-SQL)LAST_VALUE (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

傳回 SQL Server 2017SQL Server 2017 中一組經過排序之值的最後一個值。Returns the last value in an ordered set of values in SQL Server 2017SQL Server 2017.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

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

引數Arguments

scalar_expressionscalar_expression
要傳回的值。Is the value to be returned. scalar_expression 可以是資料行、子查詢,或其他結果為單一值的運算式。scalar_expression can be a column, subquery, or other expression that results in a single value. 不允許其他分析函數。Other analytic functions are not permitted.

OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
partition_by_clause 會將 FROM 子句產生的結果集分割成函數所要套用的分割區。partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. 如未指定,此函數會將查詢結果集的所有資料列視為單一群組。If not specified, the function treats all rows of the query result set as a single group.

在套用函數之前,order_by_clause 可指定資料順序。order_by_clause determines the order of the data before the function is applied. order_by_clause 為必要項目。The order_by_clause is required. rows_range_clause 會指定起始點及結束點,以進一步限制分割區中的資料列數。rows_range_clause further limits the rows within the partition by specifying start and end points. 如需詳細資訊,請參閱 OVER 子句 (Transact-SQL)For more information, see OVER Clause (Transact-SQL).

傳回類型Return Types

為與 scalar_expression 相同的類型。Is the same type as scalar_expression.

一般備註General Remarks

LAST_VALUE 不具決定性。LAST_VALUE is nondeterministic. 如需詳細資訊,請參閱 決定性與非決定性函數For more information, see Deterministic and Nondeterministic Functions.

範例Examples

A.A. 對分割區使用 LAST_VALUEUsing LAST_VALUE over partitions

下列範例會就給定的薪資 (費用),傳回每個部門中最後一名員工的雇用日期。The following example returns the hire date of the last employee in each department for the given salary (Rate). PARTITION BY 子句會依部門分割員工,而 LAST_VALUE 函數則會個別套用到每個分割區。The PARTITION BY clause partitions the employees by department and the LAST_VALUE function is applied to each partition independently. OVER 子句中指定的 ORDER BY 子句,可決定 LAST_VALUE 函數套用至每個分割區中之資料列的邏輯順序。The ORDER BY clause specified in the OVER clause determines the logical order in which the LAST_VALUE function is applied to the rows in each partition.

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');  
  

以下為結果集:Here is the result set.

  
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.B. 在計算運算式中使用 FIRST_VALUE 及 LAST_VALUEUsing FIRST_VALUE and LAST_VALUE in a computed expression

下列範例會使用計算運算式中的 FIRST_VALUE 及 LAST_VALUE 函數,分別顯示給定員工人數當季與年度第一季和最後一季銷售量配額值之間的差異。The following example uses the FIRST_VALUE and LAST_VALUE functions in computed expressions to show the difference between the sales quota value for the current quarter and the first and last quarter of the year respectively for a given number of employees. FIRST_VALUE 函數會當年度第一季的銷售配額值,並將該值從當季的銷售配額值中減去。The FIRST_VALUE function returns the sales quota value for the first quarter of the year, and subtracts it from the sales quota value for the current quarter. 其會隨附在衍生資料行 DifferenceFromFirstQuarter 中傳回。It is returned in the derived column entitled DifferenceFromFirstQuarter. 年度第一季的 DifferenceFromFirstQuarter 資料行值為 0。For the first quarter of a year, the value of the DifferenceFromFirstQuarter column is 0. LAST_VALUE 函數會傳回年度最後一季的銷售額值,並會從本季的銷售額值減去該值。The LAST_VALUE function returns the sales quota value for the last quarter of the year, and subtracts it from the sales quota value for the current quarter. 其會隨附在衍生資料行 DifferenceFromLastQuarter 中傳回。It is returned in the derived column entitled DifferenceFromLastQuarter. 對於當年度的最後一季,DifferenceFromLastQuarter 資料行的值為 0。For the last quarter of a year, the value of the DifferenceFromLastQuarter column is 0.

在此範例中,若要在 DifferenceFromLastQuarter 資料行中傳回非零的值,必須要有 "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" 子句,如下所示。The clause "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" is required in this example for the non-zero values to be returned in the DifferenceFromLastQuarter column, as shown below. 預設範圍為 "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"。The default range is "RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW". 在此範例中,使用該預設範圍 (或不包含範圍而使用預設值) 會導致 DifferenceFromLastQuarter 資料行中傳回零。In this example, using that default range (or not including a range, resulting in the default being used) would result in zeroes being returned in the DifferenceFromLastQuarter column. 如需詳細資訊,請參閱 OVER 子句 (Transact-SQL)For more information, see OVER Clause (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;  

以下為結果集:Here is the result set.

BusinessEntityID Quarter     SalesYear   QuotaThisQuarter      DifferenceFromFirstQuarter DifferenceFromLastQuarter  
---------------- ----------- ----------- --------------------- --------------------------- -----------------------  
274              1           2006        91000.00              0.00                        -63000.00  
274              2           2006        140000.00             49000.00                    -14000.00  
274              3           2006        70000.00              -21000.00                   -84000.00  
274              4           2006        154000.00             63000.00                    0.00  
274              1           2007        107000.00             0.00                        -9000.00  
274              2           2007        58000.00              -49000.00                   -58000.00  
274              3           2007        263000.00             156000.00                   147000.00  
274              4           2007        116000.00             9000.00                     0.00  
274              1           2008        84000.00              0.00                        -103000.00  
274              2           2008        187000.00             103000.00                   0.00  
275              1           2006        502000.00             0.00                        -822000.00  
275              2           2006        550000.00             48000.00                    -774000.00  
275              3           2006        1429000.00            927000.00                   105000.00  
275              4           2006        1324000.00            822000.00                   0.00  
275              1           2007        729000.00             0.00                        -489000.00  
275              2           2007        1194000.00            465000.00                   -24000.00  
275              3           2007        1575000.00            846000.00                   357000.00  
275              4           2007        1218000.00            489000.00                   0.00  
275              1           2008        849000.00             0.00                        -20000.00  
275              2           2008        869000.00             20000.00                    0.00  
  
(20 row(s) affected)