你的位置:首页 > 软件开发 > 数据库 > 开窗函数 First_Value 和 Last_Value

开窗函数 First_Value 和 Last_Value

发布时间:2016-07-12 16:00:14
在Sql server 2012里面,开窗函数丰富了许多,其中带出了2个新的函数 First_Value 和 Last Value .现在来介绍一下这2个函数的应用场景.首先分析一下First_Value(),用法是根据Partition By对数据进行分区,如果忽略Partit ...

在Sql server 2012里面,开窗函数丰富了许多,其中带出了2个新的函数 First_Value 和 Last Value .现在来介绍一下这2个函数的应用场景.

首先分析一下First_Value(),用法是根据Partition By对数据进行分区,如果忽略Partition By ,那么默认整块数据一个区域,然后根据Order By 进行排序,取出第一个值。

;WITH CTE AS(SELECT 1 AS ID ,'2016-06-01' AS DT,'A' AS UName,135 AS TotalAmount UNION ALLSELECT 2 AS ID ,'2016-06-05' AS DT,'A' AS UName,148 AS TotalAmount UNION ALLSELECT 3 AS ID ,'2016-06-02' AS DT,'B' AS UName,120 AS TotalAmount UNION ALLSELECT 4 AS ID ,'2016-06-06' AS DT,'B' AS UName,153 AS TotalAmount UNION ALLSELECT 5 AS ID ,'2016-06-10' AS DT,'B' AS UName,198 AS TotalAmount )SELECT * ,    FIRST_VALUE(CTE.TotalAmount) OVER (PARTITION BY CTE.UName ORDER BY CTE.ID) AS FirstDeal,    FIRST_VALUE(CTE.DT) OVER (PARTITION BY CTE.UName ORDER BY CTE.ID) AS FirstDate  FROM CTE

ID DT UName TotalAmount FirstDeal FirstDate

原标题:开窗函数 First_Value 和 Last_Value

关键词:函数

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。