在SqlServer中如何找到列中断缺的值并补充上

本文介绍在 SQL Server 2005 中检测表列中缺失数值的三种方法,并提供具体的 SQL 代码示例来查找和填充这些缺失值。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

--在Sql Server 2005 中如何找到列中断缺的值,并补充上.

USE tempdb;
GO
IF OBJECT_ID('dbo.T1'IS NOT NULL
  
DROP TABLE dbo.T1
GO
CREATE TABLE dbo.T1(col1 INT NOT NULL PRIMARY KEY);
INSERT INTO dbo.T1(col1) VALUES(1);
INSERT INTO dbo.T1(col1) VALUES(2);
INSERT INTO dbo.T1(col1) VALUES(3);
INSERT INTO dbo.T1(col1) VALUES(100);
INSERT INTO dbo.T1(col1) VALUES(101);
INSERT INTO dbo.T1(col1) VALUES(103);
INSERT INTO dbo.T1(col1) VALUES(104);
INSERT INTO dbo.T1(col1) VALUES(105);
INSERT INTO dbo.T1(col1) VALUES(106);


--------------------方法一---------------------

--找出断值下界部分
SELECT col1
FROM dbo.T1 AS A
WHERE NOT EXISTS
  (
SELECT * FROM dbo.T1 AS B
   
WHERE B.col1 = A.col1 + 1);
--去掉多余的断值下界,最后一行
SELECT col1 + 1 AS start_range
FROM dbo.T1 AS A
WHERE NOT EXISTS
  (
SELECT * FROM dbo.T1 AS B
   
WHERE B.col1 = A.col1 + 1)
  
AND col1 < (SELECT MAX(col1) FROM dbo.T1);

--断值上界
SELECT col1
FROM dbo.T1 AS A
WHERE NOT EXISTS
  (
SELECT * FROM dbo.T1 AS B
   
WHERE B.col1 = A.col1 - 1);
--去掉多余的断值上界,第一行
SELECT col1 - 1 AS start_range
FROM dbo.T1 AS A
WHERE NOT EXISTS
  (
SELECT * FROM dbo.T1 AS B
   
WHERE B.col1 = A.col1 - 1)
  
AND col1 > (SELECT min(col1) FROM dbo.T1);


----------------方法二--------------------------

--相邻两行的值
SELECT col1 AS cur,
  (
SELECT MIN(col1) FROM dbo.T1 AS B
   
WHERE B.col1 > A.col1) AS nxt
FROM dbo.T1 AS A;

--找出断值的两行(当前行和下一行相差值大于一) 
SELECT cur + 1 AS start_range, nxt - 1 AS end_range
FROM (SELECT col1 AS cur,
        (
SELECT MIN(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'is not null)
    
drop table Nums
create table Nums( 
int
)
declare @num int
set @num = 1
while @num<10000
begin 
insert into Nums values (@num)
set @num=@num+1
end
--利用辅助表找出所有缺值
SELECT n FROM dbo.Nums
WHERE n BETWEEN (SELECT MIN(col1) FROM dbo.T1)
            
AND (SELECT MAX(col1) FROM dbo.T1)
  
AND NOT EXISTS(SELECT * FROM dbo.T1 WHERE col1 = n);


--找出缺值,自然很容易就可以把缺值插入表中,举一列如下:
insert into dbo.T1
SELECT n FROM dbo.Nums
WHERE n BETWEEN (SELECT MIN(col1) FROM dbo.T1)
            
AND (SELECT MAX(col1) FROM dbo.T1)
  
AND NOT EXISTS(SELECT * FROM dbo.T1 WHERE col1 = n);



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值