--在Sql Server 2005 中如何找到列中断缺的值,并补充上. USE tempdb; GO IFOBJECT_ID('dbo.T1') ISNOTNULL DROPTABLE dbo.T1 GO CREATETABLE dbo.T1(col1 INTNOTNULLPRIMARYKEY); INSERTINTO dbo.T1(col1) VALUES(1); INSERTINTO dbo.T1(col1) VALUES(2); INSERTINTO dbo.T1(col1) VALUES(3); INSERTINTO dbo.T1(col1) VALUES(100); INSERTINTO dbo.T1(col1) VALUES(101); INSERTINTO dbo.T1(col1) VALUES(103); INSERTINTO dbo.T1(col1) VALUES(104); INSERTINTO dbo.T1(col1) VALUES(105); INSERTINTO dbo.T1(col1) VALUES(106); --------------------方法一--------------------- --找出断值下界部分 SELECT col1 FROM dbo.T1 AS A WHERENOTEXISTS (SELECT*FROM dbo.T1 AS B WHERE B.col1 = A.col1 +1); --去掉多余的断值下界,最后一行 SELECT col1 +1AS start_range FROM dbo.T1 AS A WHERENOTEXISTS (SELECT*FROM dbo.T1 AS B WHERE B.col1 = A.col1 +1) AND col1 < (SELECTMAX(col1) FROM dbo.T1); --断值上界 SELECT col1 FROM dbo.T1 AS A WHERENOTEXISTS (SELECT*FROM dbo.T1 AS B WHERE B.col1 = A.col1 -1); --去掉多余的断值上界,第一行 SELECT col1 -1AS start_range FROM dbo.T1 AS A WHERENOTEXISTS (SELECT*FROM dbo.T1 AS B WHERE B.col1 = A.col1 -1) AND col1 > (SELECTmin(col1) FROM dbo.T1); ----------------方法二-------------------------- --相邻两行的值 SELECT col1 AS cur, (SELECTMIN(col1) FROM dbo.T1 AS B WHERE B.col1 > A.col1) AS nxt FROM dbo.T1 AS A; --找出断值的两行(当前行和下一行相差值大于一) SELECT cur +1AS start_range, nxt -1AS end_range FROM (SELECT col1 AS cur, (SELECTMIN(col1) FROM dbo.T1 AS B WHERE B.col1 > A.col1) AS nxt FROM dbo.T1 AS A) AS D WHERE nxt - cur >1; ------------------------------方法三 --创建辅助表Nums,据说sql server 2008会在数据库中自带该表, --关于辅助表的创建,有几个经典的例子,以后再写上来. --这里只是用效率最低的方法创建一个1W行的辅助表,只够这里用. if(object_id('Nums') isnotnull) droptable Nums createtable Nums( n int ) declare@numint set@num=1 while@num<10000 begin insertinto Nums values (@num) set@num=@num+1 end --利用辅助表找出所有缺值 SELECT n FROM dbo.Nums WHERE n BETWEEN (SELECTMIN(col1) FROM dbo.T1) AND (SELECTMAX(col1) FROM dbo.T1) ANDNOTEXISTS(SELECT*FROM dbo.T1 WHERE col1 = n); --找出缺值,自然很容易就可以把缺值插入表中,举一列如下: insertinto dbo.T1 SELECT n FROM dbo.Nums WHERE n BETWEEN (SELECTMIN(col1) FROM dbo.T1) AND (SELECTMAX(col1) FROM dbo.T1) ANDNOTEXISTS(SELECT*FROM dbo.T1 WHERE col1 = n);