[转]SQL Server 2012 的 T-SQL 新功能 – 新的数据分析函数（LEAD、LAG）

当您需要在 SQL Server 中利用 T-SQL 比较结果集的每一列跟前一列或后一列的差异时，在过去可能需要利用 CURSOR 搭配临时表变量，或是透过递归 CTE 来达到这个效果，如今 SQL Server 2012 提供了两个分析用的函数（LEAD、LAG）来让您更容易进行 ROW LEVEL 数据比较。

• 以下程序代码用来示范如何透过 LEAD 函数来计算每一列与后一列的 c2 字段相差几天：

   1:  declare @t table
   2:  (
   3:      c1 int identity
   4:      ,c2 date
   5:  )
   6:  
   7:  insert into @t (c2)
   8:  select '20120101' 
   9:  union all
  10:  select '20120201' 
  11:  union all
  12:  select '20120110' 
  13:  union all
  14:  select '20120221' 
  15:  union all
  16:  select '20120121' 
  17:  union all
  18:  select '20120203' 
  19:  
  20:  select c1,c2
  21:      ,LEAD(c2) OVER (ORDER BY c2) as next_c2
  22:      ,DateDiff(day,c2,LEAD(c2) OVER (ORDER BY c2)) as diff
  23:  from @t
  24:  order by c2

 

执行结果：

 

 

• 以下程序代码用来示范如何透过 LAG  函数来计算每一列与前一列的 c2 字段相差几天：

   1:  declare @t table
   2:  (
   3:      c1 int identity
   4:      ,c2 date
   5:  )
   6:  
   7:  insert into @t (c2)
   8:  select '20120101' 
   9:  union all
  10:  select '20120201' 
  11:  union all
  12:  select '20120110' 
  13:  union all
  14:  select '20120221' 
  15:  union all
  16:  select '20120121' 
  17:  union all
  18:  select '20120203' 
  19:  
  20:  select c1,c2
  21:      ,LAG(c2) OVER (ORDER BY c2) as previous_c2
  22:      ,DateDiff(day,LAG(c2) OVER (ORDER BY c2),c2) as diff
  23:  from @t
  24:  order by c2

 

执行结果：