只能处理2048以内的流水号
with cte as
(
select ID,流水号
from 表A
),
CTE1 AS
(
select distinct number,a.id from master..spt_values as m cross join cte as a
where type='p' and number<(select MAX(b.流水号) from cte as b where b.id = a.id)
and number>=(select MIN(k.流水号) from cte as k where k.id = a.id)
)
SELECT c.id,number+1 AS 流水号 FROM CTE1 as c LEFT JOIN CTE as d
ON c.number=d.流水号-1 and c.id=d.id
WHERE d.流水号 IS NULL
order by c.id,流水号
如果要处理大于2048的流水号，需要自己创建一个完整的流水号表做辅助
[此贴子已经被作者于2019/8/27 17:33:00编辑过]
加好友 
发短信
Post By：2019/8/27 17:33:00 [显示全部帖子]
