求sql，先分组后修改值

以id分组 date 排序，分组后每组第一条的 no 不变，从第二条开始 原始no - 前一行的no = 现在的no
结果大概是：
100 2013-01-29  9
1006 2013-02-17  9
1006 2013-02-28  7
1006 2013-03-12  20

请问这个sql应该怎么写？
------解决方案--------------------

select IDENTITY(int,1,1)pid,* into #t from(

select 100 id,convert(date,'2013-01-29')date,  9 no

union all select 1006, '2013-02-17',  9

union all select 1006, '2013-02-28',  16

union all select 1006, '2013-03-12',  36

)a



select a.*,a.no-isnull(b.no,0) from #t a left join #t b on a.id=b.id and a.pid=b.pid+1



/*

pid	id	date	no	(无列名)

1	100	2013-01-29	9	9

2	1006	2013-02-17	9	9

3	1006	2013-02-28	16	7

4	1006	2013-03-12	36	20

*/

------解决方案--------------------

select a.id,convert(nvarchar(10),a.date,120) date ,a.no - isnull(b.no,0) no

from  (

select id,row_number() over (partition by id order by pid ) num,date,no

from test_demo) a left join (

select id,row_number() over (partition by id order by pid ) num,date,no

from test_demo)

b on a.id = b.id and a.num = b.num + 1 

/*

id,date,no

100,2013-01-29,9

100,2013-03-28,19

1006,2013-02-17,9

1006,2013-02-28,7

1006,2013-03-12,20



(5 行受影响)

------解决方案--------------------
楼主的问题是“先分组后修改值”，请问还要修改原始表的记录吗？
如果要的话，则：
select IDENTITY(int,1,1)pid,id,[date],[no] into #t from Test_Demo
update Test_Demo t1
set t1.[no]=t2.No_upd
from (select a.*,a.no-isnull(b.no,0) as No_upd from #t a left join #t b on a.id=b.id and a.pid=b.pid+1)t2
where t1.id=t2.id and t1.[date]=t2.[date]

--以后建表时，不要使用SQL server的保留字了（如date等）
------解决方案--------------------

--数据临测表

CREATE TABLE Test_Demo

(

	pid int primary key identity(1,1),	--主键，自增1

	id  nvarchar(20) not null,	--组

	[date] datetime not null,	--日期

	[no]	int not null		--编号

)

GO



INSERT INTO  Test_Demo SELECT '100','2013-01-29','9'

	UNION ALL SELECT '1006','2013-02-17','9' 

	UNION ALL SELECT '1006','2013-02-28','16' 

	UNION ALL SELECT '1006','2013-03-12','36' 

	UNION ALL SELECT '100','2013-03-28','28' 



select m.id , m.date , m.no - isnull((select top 1 no from test_demo n where n.id = m.id and n.pid < m.pid order by n.pid desc),0) from test_demo m

order by m.id , m.pid



drop table test_demo



/*

id                   date     &