如何查找删除后表中不可用的随机记录?有可能发现吗

Question

我有一个名为Student的表，表列是Id(identity Key(&学生姓名。它有100条记录，从1到100。我从学生表中随机删除了5条记录。现在该表只有95条记录。我如何获得逻辑表中没有的5个id。有人能帮忙找出正确的查询吗？我试图通过获取每个记录id都在增加的记录来编写查询"；1〃；在循环中，如果有任何不匹配的记录，该记录将进入临时表，然后循环将继续到最后一个记录第100个。面试官说这是不正确的方式，并没有告诉任何人要知道。我不知道这是否可能。

Answer 1

您将使用output子句：

declare @ids table (id int);
delete s
output deleted.id into @ids
from student s
where . . .;
select *
from @ids;

Answer 2

它可以工作，但不适用于第一个ID

select s1.id+1  from student s1
left join student s2 on s1.id = s2.id +1
where s2.name is null 

它可以用于第一个ID，而不能用于最后一个ID

select s1.id-1  from student s1
left join student s2 on s1.id-1 = s2.id 
where s2.name is null 

Answer 3

我认为问题是找到在过去从一个表中删除的标识值，而不是在删除时如何在运行时将ID输出到另一个表。

如果你想从你的表中找出丢失的身份，请使用：

;WITH Missing (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select IDENT_CURRENT('Your_Table'))
UNION ALL
SELECT missnum + 1, maxid FROM Missing
WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN Your_Table tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0); 

想法是使用递归CTE生成一个包含所有标识值的表，然后与删除了ID的表进行左联接。

如果您想在运行时输出delete语句正在删除的行，那么上面的@Gordon-Linoff解决方案是正确的。

Answer 4

如果我正确理解您的查询，您可以：

1. 使用CTE列出给定范围内的所有ID
2. 从CTE中选择表中不存在的所有ID

WITH N(V) AS (
SELECT 1 
UNION ALL
SELECT V + 1 FROM N
WHERE V < 100
)
SELECT *
FROM N
where V not in (select id from your_table)

我确信这是否是一个超级优雅的解决方案，涵盖了所有可能的场景，但应该在大多数典型情况下完成工作。

Answer 5

试试这样的东西：

1. 删除前复制Student表
2. 处理随机删除
3. 然后尝试：

   SELECT    * 
   FROM      Student 
   EXCEPT 
   SELECT    * 
   FROM      StudentDuplicated