寻找错过的ID在SQL Server 2008

问题描述：

我有一个包含两列寻找错过的ID在SQL Server 2008

ID | Name 
---------------- 
1 | John 
2 | Sam 
3 | Peter 
6 | Mike

它已经错过了ID的表。在这种情况下，这些是4和5。

如何查找并将它们与随机名一起插入到此表中？

更新：不允许使用游标和临时表。随机的名字应该是'Name _'+一些随机数字。也许它会是像'Abby'这样的特定值。所以没关系。

当你说随机的名字，一张桌子，你真的意味着你正在寻找填补未使用的插槽与数据（其他的名字？）或者，你真诚地期待生成随机的其他名字？ – 2012-04-11 18:11:51

我想你需要详细阐述一下RANDOM的名字，以便人们在这方面提供帮助。 – 2012-04-11 18:27:11

为什么要这么做？如果将新值插入跳过的id，特别是如果它们是身份，因为它们很可能在大多数数据库中，那么为什么要这么做呢？闻起来像功课。 – HLGEM 2012-04-11 18:46:40

答

使用递归CTE可以确定失踪的ID如下

DECLARE @Table TABLE(
     ID INT, 
     Name VARCHAR(10) 
) 

INSERT INTO @Table VALUES (1, 'John'),(2, 'Sam'),(3,'Peter'),(6, 'Mike') 

DECLARE @StartID INT, 
     @EndID INT 

SELECT @StartID = MIN(ID), 
     @EndID = MAX(ID) 
FROM @Table 

;WITH IDS AS (
     SELECT @StartID IDEntry 
     UNION ALL 
     SELECT IDEntry + 1 
     FROM IDS 
     WHERE IDEntry + 1 <= @EndID 
) 
SELECT IDS.IDEntry [ID] 
FROM IDS LEFT JOIN 
     @Table t ON IDS.IDEntry = t.ID 
WHERE t.ID IS NULL 
OPTION (MAXRECURSION 0)

选项MAXRECURSION 0将允许代码，以避免SQL SERVER的递归限制

从Query Hints和WITH common_table_expression (Transact-SQL)

MAXRECURSION number指定此查询允许的最大递归次数。数字是介于0和之间的非负整数32767.指定0时，不应用限制。如果未指定此选项，则服务器的默认限制为100.

在查询执行期间达到MAXRECURSION限制的指定或默认数量时，查询结束并返回错误。

由于此错误，语句的所有效果都会回滚。如果语句是SELECT语句，则可能会返回部分结果或无结果。返回的任何部分结果可能不包括超出指定最大递归级别的递归级别上的所有行。

产生RANDOM名称将大量迁入通过这样一个名字的需求受到影响，这样的名称的列类型。这个随机名称到底是什么？

整洁。使我想要删除我的答案。你的回答并不是他的问题的插入部分，但这对最后几行是一个微不足道的变化。 – 2012-04-11 18:18:55

+1 - 很好的答案，你打我几秒钟:) – 2012-04-11 18:19:21

答

首先选择表中最高的数字（select top 1 id desc），或选择max（id），然后运行while循环以从1 ... max迭代。

见this article about looping.

对于每次迭代，看是否行存在，如果没有，插入到表中，与该ID。

答

您可以使用递归公用表达式CTE来做到这一点。下面是一个例子如何：

DECLARE @MaxId INT 
SELECT @MaxId = MAX(ID) from MyTable 
;WITH Numbers(Number) AS 
(
    SELECT 1 
    UNION ALL 
    SELECT Number + 1 FROM Numbers WHERE Number < @MaxId 
) 
SELECT n.Number, 'Random Name' 
FROM Numbers n 
LEFT OUTER JOIN MyTable t ON n.Number=t.ID 
WHERE t.ID IS NULL

这里有关于热膨胀系数，这将有助于Using Common Table Expressions和Recursive Queries Using Common Table Expressions

答

我觉得递归CTE是一个更好的解决方案几篇文章，因为这将是更快，但在这里对我来说是什么工作：

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U')) 
DROP TABLE [dbo].[TestTable] 
GO 

CREATE TABLE [dbo].[TestTable](
    [Id] [int] NOT NULL, 
    [Name] [varchar](50) NOT NULL, 
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC 
)) 

GO 

INSERT INTO [dbo].[TestTable]([Id],[Name]) VALUES (1, 'John') 
INSERT INTO [dbo].[TestTable]([Id],[Name]) VALUES (2, 'Sam') 
INSERT INTO [dbo].[TestTable]([Id],[Name]) VALUES (3, 'Peter') 
INSERT INTO [dbo].[TestTable]([Id],[Name]) VALUES (6, 'Mike') 
GO 

declare @mod int 
select @mod = MAX(number)+1 from master..spt_values where [type] = 'P' 

INSERT INTO [dbo].[TestTable] 
SELECT y.Id,'Name_' + cast(newid() as varchar(45)) Name from 
(
    SELECT TOP (select MAX(Id) from [dbo].[TestTable]) x.Id from 
    (
     SELECT 
     t1.number*@mod + t2.number Id 
     FROM master..spt_values t1 
     CROSS JOIN master..spt_values t2 
     WHERE t1.[type] = 'P' and t2.[type] = 'P' 
    ) x 
    WHERE x.Id > 0 
    ORDER BY x.Id 
) y 
LEFT JOIN [dbo].[TestTable] on [TestTable].Id = y.Id 
where [TestTable].Id IS NULL 
GO 

select * from [dbo].[TestTable] 
order by Id 
GO

http://www.sqlfiddle.com/#!3/46c7b/18

答

这其实很简单：

创建一个名为#All_numbers的表格，其中应包含您正在查找的范围内的所有自然数。

#list是包含数据

select a.num as missing_number , 
     'Random_Name' + convert(varchar, a.num) 
from #All_numbers a left outer join #list l on a.num = l.Id 
where l.id is null

临时表是不允许的。 – Alexandre 2012-04-12 20:15:05

寻找错过的ID在SQL Server 2008

相关推荐