FIRST_VALUE (Transact-SQL)FIRST_VALUE (Transact-SQL)

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

傳回 SQL Server 2017SQL Server 2017 中排序值集的第一個值。Returns the first value in an ordered set of values in SQL Server 2017SQL Server 2017.

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

語法Syntax

FIRST_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 arbitrary 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 logical order in which the operation is performed. order_by_clause 為必要項目。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

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

範例Examples

A.A. 針對查詢結果集使用 FIRST_VALUEUsing FIRST_VALUE over a query result set

下列範例使用 FIRST_VALUE 傳回給定產品類別目錄中最便宜產品的名稱。The following example uses FIRST_VALUE to return the name of the product that is the least expensive in a given product category.

USE AdventureWorks2012;  
GO  
SELECT Name, ListPrice,   
       FIRST_VALUE(Name) OVER (ORDER BY ListPrice ASC) AS LeastExpensive   
FROM Production.Product  
WHERE ProductSubcategoryID = 37;  

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

  
Name                    ListPrice             LeastExpensive  
----------------------- --------------------- --------------------  
Patch Kit/8 Patches     2.29                  Patch Kit/8 Patches  
Road Tire Tube          3.99                  Patch Kit/8 Patches  
Touring Tire Tube       4.99                  Patch Kit/8 Patches  
Mountain Tire Tube      4.99                  Patch Kit/8 Patches  
LL Road Tire            21.49                 Patch Kit/8 Patches  
ML Road Tire            24.99                 Patch Kit/8 Patches  
LL Mountain Tire        24.99                 Patch Kit/8 Patches  
Touring Tire            28.99                 Patch Kit/8 Patches  
ML Mountain Tire        29.99                 Patch Kit/8 Patches  
HL Road Tire            32.60                 Patch Kit/8 Patches  
HL Mountain Tire        35.00                 Patch Kit/8 Patches  
  

B.B. 針對整個分割區使用 FIRST_VALUEUsing FIRST_VALUE over partitions

下列範例使用 FIRST_VALUE 傳回相同職稱的員工中,休假時數最少的員工。The following example uses FIRST_VALUE to return the employee with the fewest number of vacation hours compared to other employees with the same job title. PARTITION BY 子句會依職稱分割員工,而 FIRST_VALUE 函數會個別套用至每個分割區。The PARTITION BY clause partitions the employees by job title and the FIRST_VALUE function is applied to each partition independently. OVER 子句中指定的 ORDER BY 子句,可決定 FIRST_VALUE 函數套用至每個分割區中之資料列的邏輯順序。The ORDER BY clause specified in the OVER clause determines the logical order in which the FIRST_VALUE function is applied to the rows in each partition. ROWS UNBOUNDED PRECEDING 子句會將視窗起點指定為每個分割區的第一列。The ROWS UNBOUNDED PRECEDING clause specifies the starting point of the window is the first row of each partition.

USE AdventureWorks2012;   
GO  
SELECT JobTitle, LastName, VacationHours,   
       FIRST_VALUE(LastName) OVER (PARTITION BY JobTitle   
                                   ORDER BY VacationHours ASC  
                                   ROWS UNBOUNDED PRECEDING  
                                  ) AS FewestVacationHours  
FROM HumanResources.Employee AS e  
INNER JOIN Person.Person AS p   
    ON e.BusinessEntityID = p.BusinessEntityID  
ORDER BY JobTitle;  

以下為部分結果集。Here is a partial result set.

  
JobTitle                            LastName                  VacationHours FewestVacationHours  
----------------------------------- ------------------------- ------------- -------------------  
Accountant                          Moreland                  58            Moreland  
Accountant                          Seamans                   59            Moreland  
Accounts Manager                    Liu                       57            Liu  
Accounts Payable Specialist         Tomic                     63            Tomic  
Accounts Payable Specialist         Sheperdigian              64            Tomic  
Accounts Receivable Specialist      Poe                       60            Poe  
Accounts Receivable Specialist      Spoon                     61            Poe  
Accounts Receivable Specialist      Walton                    62            Poe  

另請參閱See Also

OVER 子句 (Transact-SQL)OVER Clause (Transact-SQL)