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

时间:2015-09-11 10:19:46   收藏:0   阅读:444
 1  CREATE TABLE #tempsource(col NVARCHAR(100))
 2 
 3 INSERT INTO #tempsource (col) VALUES(20140100001)
 4 INSERT INTO #tempsource (col) VALUES(20140100002)
 5 INSERT INTO #tempsource (col) VALUES(20140100003)
 6 INSERT INTO #tempsource (col) VALUES(20140100004)
 7 INSERT INTO #tempsource (col) VALUES(20140100007)
 8 INSERT INTO #tempsource (col) VALUES(20140100008)
 9 INSERT INTO #tempsource (col) VALUES(201401000010)
10 INSERT INTO #tempsource (col) VALUES(201401000011)
11 INSERT INTO #tempsource (col) VALUES(20140200001)
12 INSERT INTO #tempsource (col) VALUES(20140200002)
13 INSERT INTO #tempsource (col) VALUES(20140200003)
14 INSERT INTO #tempsource (col) VALUES(20140200004)
15 INSERT INTO #tempsource (col) VALUES(20140200007)
16 INSERT INTO #tempsource (col) VALUES(20140200009)
17 INSERT INTO #tempsource (col) VALUES(20140200010)
18 INSERT INTO #tempsource (col) VALUES(20140200011)

 技术分享

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(0000000+ CAST(StartNum AS NVARCHAR),5) AS StartNum ,name +RIGHT(0000000+ CAST(EndNum AS NVARCHAR),5) AS EndNum,Count FROM  #tempContinuity
SELECT name +RIGHT(0000000+ CAST(LostStartNum AS NVARCHAR),5) AS LostStartNum ,name +RIGHT(0000000+ CAST(LostEndNum AS NVARCHAR),5) AS LostEndNum,LostCount FROM  #tempLost

 技术分享

    SELECT name,STUFF((SELECT name +RIGHT(0000000+ CAST(StartNum AS NVARCHAR),5) +-+name +RIGHT(0000000+ 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

技术分享

原文:http://www.cnblogs.com/mingxh/p/4800001.html

评论(0
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣,分享程序人生!