正文

sql从某不连续的数字中将其分段并找出缺失的数字并分段

IP云
IP云V管理员/3 阅读
08/12
小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序
  • 首先做准备数据

CREATE TABLE #tempsource(col NVARCHAR(100)) INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')
INSERT INTO #tempsource (col) VALUES('')

  • 将数据转换成应该处理的数据格式

CREATE TABLE #temp(name NVARCHAR(50),colValue INT ) INSERT INTO #temp SELECT LEFT(col,6) AS name ,SUBSTRING(col,7,LEN(col)) FROM #tempsource

  • 将数据进行排序分组

select v1.colValue, (v1.colValue - v1.rownum) as delta ,v1.name INTO #tempSort
from
(
select Row_Number() OVER (ORDER BY name,colValue) as rownum,colValue ,name
from #temp
) v1

  • 找出连续的数字并分组

SELECT v2.name,delta,MIN(v2.colValue) as StartNum, max(v2.colValue) as EndNum,
max(v2.colValue)-min(v2.colValue)+1 as Count,ROW_NUMBER() OVER( ORDER BY v2.delta ASC ) AS rownum
INTO #tempContinuity
from #tempSort v2
--WHERE v2.colValue >=2
group by v2.name,v2.delta
ORDER BY StartNum

  • 跟据连续的信息找出不连续的数字并分组

SELECT tlx1.name,tlx1.EndNum+1 LostStartNum,tlx2.StartNum-1 AS LostEndNum,tlx2.StartNum-1 -(tlx1.EndNum+1)+1 LostCount
INTO #tempLost
FROM #tempContinuity AS tlx1
LEFT JOIN #tempContinuity AS tlx2 ON tlx1.rownum+1 = tlx2.rownum AND tlx2.name = tlx1.name
WHERE tlx2.StartNum IS NOT NULL
ORDER BY tlx1.delta

  • 将取得的信息进行组装成源格式进行显示

SELECT name +RIGHT(''+ CAST(StartNum AS NVARCHAR),5) AS StartNum ,name +RIGHT(''+ CAST(EndNum AS NVARCHAR),5) AS EndNum,Count FROM #tempContinuity
SELECT name +RIGHT(''+ CAST(LostStartNum AS NVARCHAR),5) AS LostStartNum ,name +RIGHT(''+ CAST(LostEndNum AS NVARCHAR),5) AS LostEndNum,LostCount FROM #tempLost

  • 使用ForXml将其分组合并成一行

SELECT name,STUFF((SELECT name +RIGHT(''+ CAST(StartNum AS NVARCHAR),5) +'-'+name +RIGHT(''+ CAST(EndNum AS NVARCHAR),5)+'|'+CAST(t.Count AS NVARCHAR) +';' FROM #tempContinuity AS t WHERE t.name =v.name FOR XML PATH ('')),1,0,'') AS strCard
FROM #tempContinuity AS v
GROUP BY v.name