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
