select id + ( select sum(id) from tbl b where a.id < b.id and b.id < (select top 1 id from tbl c where b.id <id and not exists (select 1 from tbl where c.id -1 =id )order by id asc ) ) from tbl a where not exists (select 1 from tbl where a.id -1 =id )
--创建数据 declare @ta table (id int)insert @ta select 100 union select 101 union select 103 union select 105 union select 106 union select 108select * from @ta --生成 中间表 declare @tb table(id int,num int) insert @tb select id ,(select count(1) from @ta where id<=a.id + 1) from @ta a --结果 select sum(id) as id from @tb group by num /*id ----------- 201 103 211 108 */
select sum(id) from tbl b
where a.id < b.id and b.id <
(select top 1 id from tbl c where b.id <id and not exists (select 1 from tbl where c.id -1 =id )order by id asc )
)
from tbl a
where not exists (select 1 from tbl where a.id -1 =id )
declare @ta table (id int)insert @ta select 100 union select 101 union select 103 union select 105 union select 106 union select 108select * from @ta
--生成 中间表
declare @tb table(id int,num int)
insert @tb select id ,(select count(1) from @ta where id<=a.id + 1) from @ta a
--结果
select sum(id) as id
from @tb
group by num
/*id
-----------
201
103
211
108
*/