DAY 8
4

## [Denali 新特性探險8]Rows and Range

Rows and Range:允許更改分區中的開始位置和結束位置。

1.並非所有function 都支援 rows and range
2.使用 rows and range必須指定 order by
3.有指定 order by但未使用rows and range，

``````select c1,c2,sum(c2) over (order by c1 range unbounded preceding) as '累加D量1',
sum(c2) over (order by c1 range between unbounded preceding AND unbounded following) as '累加量2',
sum(c2) over (order by c1 rows between current row and 1 following ) as '累加量3',
sum(c2) over (order by c1 rows 1 preceding ) as '累加量4',
sum(c2) over (order by c1 rows between 1 preceding and 3 following  ) as '累加量5'
from dbo.mysales
``````

Denali 寫法

``````declare @starttime time(7),@endtime time(7),@elaspedtime numeric(20,7);
set @starttime=GETDATE() ;
--range unbounded preceding
select c1,c2, sum(c2) over (order by c1 range unbounded preceding) as '累加量'
from dbo.mysales
set @endtime=GETDATE();
set @elaspedtime = convert(integer, datediff(ms, @starttime, @endtime));
select @elaspedtime as '花費時間(ms)'
``````

SQL2005/2008 寫法

``````declare @result table(c1 varchar(10),c2 int,total int default(0))
declare @starttime time(7),@endtime time(7),@elaspedtime numeric(20,7);
set @starttime=GETDATE() ;
--將來源資料塞結果表
insert into @result select *,0 from dbo.mysales
declare @total int
set @total=0
update @result
--設定變數與資料行相同的值
set @total = total = @total + c2
--結果
select t1.c1,t1.c2,t1.total as '累加量'
from @result t1
set @endtime=GETDATE();
set @elaspedtime = convert(integer, datediff(ms, @starttime, @endtime));
select @elaspedtime as '花費時間(ms)'
``````

``````--Denali
select c1,c2, sum(c2) over (order by c1 range unbounded preceding) as '累加量'
from dbo.mysales
--2005/2008
declare @result table(c1 varchar(10),c2 int,total int default(0))
--將來源資料塞結果表
insert into @result select *,0 from dbo.mysales
declare @total int
set @total=0
update @result
--設定變數與資料行相同的值
set @total = total = @total + c2
--結果
select t1.c1,t1.c2,t1.total as '累加量'
from @result t1
``````

MS SQL Server新特性探險:探險代碼 Denali 全系列文章