sql server 2005 中的分区函数用法(partition by 字段)

以下例子根据Item 分组后.排序.

应用例子如下:

if   exists ( select   1   from  sys.objects  where   object_id = object_id ( ' tab ' ))
begin
     drop   table  tab
end
go
create   table  tab(item  int , date  datetime ,  temp   int )
insert  tab  select   10 , ' 2006-01-01 ' , 0
union   all   select   10 , ' 2006-02-01 ' , 0
union   all   select   10 , ' 2006-03-01 ' , 0
union   all   select   20 , ' 2006-01-01 ' , 0
union   all   select   20 , ' 2006-02-01 ' , 0
union   all   select   30 , ' 2006-01-01 ' , 0
union   all   select   30 , ' 2006-02-01 ' , 0
union   all   select   30 , ' 2006-03-01 ' , 0
union   all   select   30 , ' 2006-04-01 ' , 0
union   all   select   30 , ' 2006-05-01 ' , 0
go
select   * ,row_number()  over (partition  by  item  order   by  date  )  as  t  from  tab
go


-- 结果
/*
item        date                    temp        t
----------- ----------------------- ----------- --------------------
10          2006-01-01 00:00:00.000 0           1
10          2006-02-01 00:00:00.000 0           2
10          2006-03-01 00:00:00.000 0           3
20          2006-01-01 00:00:00.000 0           1
20          2006-02-01 00:00:00.000 0           2
30          2006-01-01 00:00:00.000 0           1
30          2006-02-01 00:00:00.000 0           2
30          2006-03-01 00:00:00.000 0           3
30          2006-04-01 00:00:00.000 0           4
30          2006-05-01 00:00:00.000 0           5

(10 行受影响)

*/