SQL SERVER 号码段合并

代码：

CREATE   TABLE  #TMP(
    ID  INT ,
    S  NVARCHAR ( 100 ),
    E  NVARCHAR ( 100 ))
 
INSERT   INTO  #TMP(ID,S,E)
SELECT   1 , 1 , 2000   UNION   ALL
SELECT   1 , 2001 , 4000   UNION   ALL
SELECT   1 , 5000 , 6000   UNION   ALL
SELECT   2 , 20001 , 30000   UNION   ALL
SELECT   2 , 40001 , 50000   UNION   ALL
SELECT   2 , 30001 , 40000

SELECT   max (a.id) id,a.s , min ( case   when  (b.e  IS   NOT   NULL )  THEN  b.e  ELSE  a.e  end )  FROM  #tmp a 
left   JOIN  #tmp b  ON   a.id = b.id  AND  a.e + 1 = b.s
WHERE    NOT   EXISTS ( SELECT   1   FROM  #tmp  WHERE  e + 1   = a.s  AND  id = a.id)
AND   NOT   EXISTS ( SELECT   1   FROM  #tmp  WHERE  s + 1 = b.e  AND  id = a.id)
GROUP   BY  a.id,a.s  ORDER   BY  id

结果

< p > id          s       e                                                                                 
1             1         4000
1             5000      6000
2             20001     40000 </ p >< p > ( 3  行受影响)
</ p >

url: http://greatverve.cnblogs.com/archive/2011/07/22/sql-Merge.html

转载于:https://www.cnblogs.com/greatverve/archive/2011/07/22/sql-Merge.html