sql中over用法

累加当日、昨日、明日：

select a,b,sum(c) over(partition by a order by b rows between 1 preceding and 1 following） from t

累加当日和昨天：

select a,b,sum(c) over(partition by a order by b rows between 1 preceding and current row) from t

累加历史：分区内当天及之前所有

select a,b,sum(c) over(partition by a order by b) from t

或者：

select a,b,sum(c) over(partition by a order by b rows between unbounded preceding and current row) from t

累加分区内所有：当天和之前之后所有

select a,b,sum(c) over(partition by a order by b rows between unbounded preceding and unbounded following) from t

over是用于数据的分区和排序，常用在函数后边使用

例如：

create table over_eg

(

row1 int,

row2 int

)

declare @int int

set @int=1

while @int<=50

begin

insert into over_eg values (@int,@int%5)

set @int=@int+1

end

go

--按照row2进行分区，找出每个区里边最小的值

select min_row1=min(row1) over (partition by row2) ,row2 from over_eg

--按照row2进行分堆，再把每堆按照row1列升序排列

select rownum=row_number() over (partition by row2 order by row1) ,row1,row2 from over_eg

over是用于数据的分区和排序，常用在函数后边使用

例如：

create table over_eg

(

row1 int,

row2 int

)

declare @int int

set @int=1

while @int<=50

begin

insert into over_eg values (@int,@int%5)

set @int=@int+1

end

go

--按照row2进行分区，找出每个区里边最小的值

select min_row1=min(row1) over (partition by row2) ,row2 from over_eg

---