34,594
社区成员
发帖
与我相关
我的任务
分享
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 1 10 3 本日发生额与余额
29 1 13 5 本日发生额与余额
10 1 13 5 业务
11 1 13 5 业务
12 1 13 5 业务
13 1 17 8 业务
14 1 17 8 业务
15 1 17 8 业务
16 1 17 8 业务
30 1 17 8 本日发生额与余额
31 1 22 9 本日发生额与余额
17 1 22 9 业务
18 1 22 9 业务
19 1 25 10 业务
20 1 25 10 业务
21 1 25 10 业务
32 1 25 10 本日发生额与余额
22 1 29 11 业务
33 1 29 11 本日发生额与余额
34 1 31 12 本日发生额与余额
23 1 31 12 业务
24 1 31 12 业务
25 1 31 12 业务
35 1 35 31 本月发生额与余额
(35 行受影响)