declare @tmp table (id1 int identity(1,1),id int,nid int,fday int,fexp nvarchar(50))
--nid为用于最后排序输出，fday为日期，先插入各天业务，再计算各天发生额与余额，再计算月发生额与余额
--最后按日期排序输出。
declare @tmp1 table (id int)
insert into @tmp (id,nid,fday,fexp)
select 1,0,1,'业务'
union all select 1,0,1,'业务' union all select 1,0,1,'业务'
union all select 1,0,2,'业务' union all select 1,0,2,'业务'
union all select 1,0,2,'业务' union all select 1,0,2,'业务'
union all select 1,0,3,'业务' union all select 1,0,3,'业务'
union all select 1,0,5,'业务' union all select 1,0,5,'业务'
union all select 1,0,5,'业务' union all select 1,0,8,'业务'
union all select 1,0,8,'业务' union all select 1,0,8,'业务'
union all select 1,0,8,'业务' union all select 1,0,9,'业务'
union all select 1,0,9,'业务' union all select 1,0,10,'业务'
union all select 1,0,10,'业务' union all select 1,0,10,'业务'
union all select 1,0,11,'业务' union all select 1,0,12,'业务'
union all select 1,0,12,'业务' union all select 1,0,12,'业务'
union all select 1,1,1,'本日发生额与余额' union all select 1,1,2,'本日发生额与余额'
union all select 1,1,3,'本日发生额与余额' union all select 1,1,5,'本日发生额与余额'
union all select 1,1,8,'本日发生额与余额' union all select 1,1,9,'本日发生额与余额'
union all select 1,1,10,'本日发生额与余额' union all select 1,1,11,'本日发生额与余额'
union all select 1,1,12,'本日发生额与余额' union all select 1,2,31,'本月发生额与余额'
insert into @tmp1 values (1)

select a.id,a.id1,count(*) nid
from @tmp a,@tmp b,@tmp1 c
where a.id=b.id and a.id=c.id and (a.fday>b.fday or a.fday=b.fday and a.nid>b.nid or a.fday=b.fday and a.nid=b.nid and a.id1>=b.id1)
group by a.id,a.id1
order by 1,3

update aa
set nid=bb.nid
from @tmp aa,
   ( select a.id,a.id1,count(*) nid
from @tmp a,@tmp b,@tmp1 c
where a.id=b.id and a.id=c.id and (a.fday>b.fday or a.fday=b.fday and a.nid>b.nid or a.fday=b.fday and a.nid=b.nid and a.id1>=b.id1)
group by a.id,a.id1) bb
where aa.id=bb.id and aa.id1=bb.id1
--update后输出结果不正确，nid不是子查询的连续整数。
select * from @tmp order by id,nid

id          id1         nid
----------- ----------- -----------
1           1           1
1           2           2
1           3           3
1           26          4
1           4           5
1           5           6
1           6           7
1           7           8
1           27          9
1           8           10
1           9           11
1           28          12
1           10          13
1           11          14
1           12          15
1           29          16
1           13          17
1           14          18
1           15          19
1           16          20
1           30          21
1           17          22
1           18          23
1           31          24
1           19          25
1           20          26
1           21          27
1           32          28
1           22          29
1           33          30
1           23          31
1           24          32
1           25          33
1           34          34
1           35          35

(35 行受影响)

(35 行受影响)

id1         id          nid         fday        fexp
----------- ----------- ----------- ----------- --------------------------------------------------
1           1           1           1           业务
2           1           2           1           业务
3           1           2           1           业务
26          1           2           1           本日发生额与余额
27          1           5           2           本日发生额与余额
4           1           5           2           业务
5           1           5           2           业务
6           1           5           2           业务
7           1           5           2           业务
8           1           10          3           业务
9           1           10          3           业务
28