实际应用中偶然发现,@@version为8.0.194
- SQL code
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(*) nidfrom @tmp a,@tmp b,@tmp1 cwhere 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.id1order by 1,3update aaset nid=bb.nidfrom @tmp aa, ( select a.id,a.id1,count(*) nidfrom @tmp a,@tmp b,@tmp1 cwhere 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) bbwhere aa.id=bb.id and aa.id1=bb.id1--update后输出结果不正确，nid不是子查询的连续整数。select * from @tmp order by id,nid
结果
- SQL code
id id1 nid----------- ----------- -----------1 1 11 2 21 3 31 26 41 4 51 5 61 6 71 7 81 27 91 8 101 9 111 28 121 10 131 11 141 12 151 29 161 13 171 14 181 15 191 16 201 30 211 17 221 18 231 31 241 19 251 20 261 21 271 32 281 22 291 33 301 23 311 24 321 25 331 34 341 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 行受影响)