SQL SERVER 2012 T-SQL新增聚合函数
来源：未知 责任编辑：责任编辑 发表时间:2013-11-17 14:41 点击:次
SQL SERVER 2012 T-SQL新增几个聚合函数:
FIRST_VALUE
LAST_VALUE
LEAD
LAG
下面练习一下.
[sql]
SELECT NAME,date,amount,
first_value(amount) OVER (PARTITION BY NAME ORDER BY date) AS firstvalue,
last_value(amount) OVER (PARTITION BY NAME ORDER BY date) AS lastvalue1,
last_value(amount) OVER (PARTITION BY NAME ORDER BY date RANGE BETWEEN unbounded preceding AND unbounded following) AS lastvalue2,
lead(amount) OVER (PARTITION BY NAME ORDER BY date) AS lead,
lag(amount) OVER (PARTITION BY NAME ORDER BY date) AS lag
FROM test www.2cto.com
需要指出的是:
LAST_VALUE,默认是当前行,因此, 要想返回预期的结果,必须使用窗口函数加以限制.
上图第一个返回的,显然不是我们想要的,第二个用了窗口函数,就正确了.
作者 incognito007
相关新闻>>
最新推荐更多>>>
- 发表评论
-
- 最新评论 进入详细评论页>>
