【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 test
go

create table test
(
id int  primary key,
name varchar(20),
groupname varchar(20)
)

insert into test
values(1,'小明','开发部'),
      (4,'小张','开发部'),
      (5,'小白','开发部'),

      (8,'小王','财务部'),
      (9, null,'财务部'),
      (15,'小刘','财务部'),

	  (16,'小高','行政部'),
      (18,'小王','行政部'),
	  (23,'小李','行政部'),
	  (29,'小吴','行政部');

 

转载于:https://www.cnblogs.com/momogua/p/8304477.html