【SQL 编程你也行】SQL Server新功能之函数：count函数(2012、2014的over partition by order by rows/range between)

在2012、2014中除了提供，over partition by order by之外，还提供了 rows/range关键字，能够取出窗口的数据。

rows 和 range：

rows表示 行，就是前n行，后n行
而range表示的是 具体的值，比这个值小n的行，比这个值大n的行

current row  当前行
unbounded following 最后1行
unbounded preceding 第1行
n following  后面n行
n preceding  前面n行

举例：

select *,       count(*) over(partition by groupname                      order by id  rows between current row and 1 following) 当前行_后1行,       count(*) over(partition by groupname                      order by id   rows between 1 preceding and current row ) 前1行_当前行,       count(*) over(partition by groupname                      order by id  rows between current row and unbounded following) 当前行_最后1行,       count(*) over(partition by groupname                      order by id   rows between unbounded preceding and current row) 第1行_当前行,   count(*) over(partition by groupname                      order by id rows between 1 preceding and 1 following) 前1行_后1行,   count(*) over(partition by groupname                      order by id   rows between unbounded preceding and 1 following) 第1行_后1行,   count(*) over(partition by groupname                      order by id   rows between 1 preceding and unbounded following) 前1行_最后1行from test 

运行结果；

附上建表语句：

if object_id('test') is not null   drop table testgocreate table test(id int  primary key,name varchar(20),groupname varchar(20))insert into testvalues(1,'小明','开发部'),      (4,'小张','开发部'),      (5,'小白','开发部'),      (8,'小王','财务部'),      (9, null,'财务部'),      (15,'小刘','财务部'),  (16,'小高','行政部'),      (18,'小王','行政部'),  (23,'小李','行政部'),  (29,'小吴','行政部');