通过

LEAD (Transact-SQL)

  • 项目

访问相同结果集的后续行中的数据，而不使用 SQL Server 2014 中的自联接。 LEAD 以当前行之后的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与后续行中的值进行比较。

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

主题链接图标 Transact-SQL 语法约定 (Transact-SQL)

语法

LEAD ( scalar_expression [ ,offset ] , [ default ] ) 
    OVER ( [ partition_by_clause ] order_by_clause )

参数

  • scalar_expression
    要返回的值基于指定的偏移量。 这是一个返回单个（标量）值的任何类型的表达式。 scalar_expression 不能为分析函数。

  • offset
    从在其中获取值的当前行前移的行数。 如果未指定，则默认值为 1。 offset 可以是列、子查询或其他求值为正整数的表达式，或者可隐式转换为 bigint。 offset 不能是负数值或分析函数。

  • default
    当偏移量为 offset 的 scalar_expression 为 NULL 时要返回的值。 如果未指定默认值，则返回 NULL。 default 可以是列、子查询或其他表达式，但它不能是分析函数。 default 的类型必须与 scalar_expression 相符。

  • OVER ( [ partition_by_clause ] order_by_clause**)**
    partition_by_clause 将由 FROM 子句生成的结果集划分成 RANK 函数适用的分区。 如果未指定，则此函数将查询结果集的所有行视为单个组。 order_by_clause 在应用函数之前确定数据的顺序。 当指定 partition_by_clause 时，它确定每个分区中数据的顺序。 order_by_clause 是必需的。 有关详细信息，请参阅 OVER 子句 (Transact-SQL)。

返回类型

指定的 scalar_expression 的数据类型。 如果 scalar_expression 可为 Null，则返回 Null；否则 default 设置为 NULL。

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

示例

A.比较年度之间的值

此查询使用 LEAD 函数返回特定员工在后续年度的销售配额差异。 请注意，因为最后一行没有提供提前值，所以将返回默认值零 (0)。

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota, 
    LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS NextQuota
FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID = 275 and YEAR(QuotaDate) IN ('2005','2006');

下面是结果集：

BusinessEntityID SalesYear   CurrentQuota          NextQuota
---------------- ----------- --------------------- ---------------------
275              2005        367000.00             556000.00
275              2005        556000.00             502000.00
275              2006        502000.00             550000.00
275              2006        550000.00             1429000.00
275              2006        1429000.00            1324000.00
275              2006        1324000.00            0.00

B.比较分区中的值

下面的示例使用 LEAD 函数比较员工之间年初至今的销售额。 指定 PARTITION BY 子句来按销售地区对结果集中的行进行分区。 LEAD 函数分别应用于每个分区，并为每个分区重新启动计算。 在应用函数之前，在 OVER 子句中指定的 ORDER BY 子句将对每个分区中的行进行排序。 SELECT 语句中的 ORDER BY 子句对整个结果集中的行进行排序。 请注意，因为每个分区的最后一行没有提供提前值，所以将返回默认值零 (0)。

USE AdventureWorks2012;
GO
SELECT TerritoryName, BusinessEntityID, SalesYTD, 
       LEAD (SalesYTD, 1, 0) OVER (PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS NextRepSales
FROM Sales.vSalesPerson
WHERE TerritoryName IN (N'Northwest', N'Canada') 
ORDER BY TerritoryName;

下面是结果集：

TerritoryName            BusinessEntityID SalesYTD              NextRepSales
-----------------------  ---------------- --------------------- ---------------------
Canada                   282              2604540.7172          1453719.4653
Canada                   278              1453719.4653          0.00
Northwest                284              1576562.1966          1573012.9383
Northwest                283              1573012.9383          1352577.1325
Northwest                280              1352577.1325          0.00

C.指定任意表达式

下面的示例演示如何在 LEAD 函数语法中指定各种任意表达式。

CREATE TABLE T (a int, b int, c int); 
GO
INSERT INTO T VALUES (1, 1, -3), (2, 2, 4), (3, 1, NULL), (4, 3, 1), (5, 2, NULL), (6, 1, 5); 

SELECT b, c, 
    LEAD(2*c, b*(SELECT MIN(b) FROM T), -c/2.0) OVER (ORDER BY a) AS i
FROM T;

下面是结果集：

b           c           i
----------- ----------- -----------
1           -3          8
2           4           2
1           NULL        2
3           1           0
2           NULL        NULL
1           5           -2

请参阅

参考

LAG (Transact-SQL)