如何在 SQL Server 2008 R2 中不使用游标填充表

Question

我需要识别编号连续的缺失产品。换句话说，我们每家商店出售的一组产品都有唯一的编号。最终用户想要一份能够识别日期范围内丢失的小部件的报告。

Store   Product Number
Store 1  Widget   100
Store 1  Widget   101
Store 1  Widget   102
Store 1  Widget   104
Store 2  Widget   201
Store 2  Widget   202
Store 2  Widget   203

我认为最好的方法是获取 MAX(Number) 和 MIN(Number)，然后用序列中的所有数字填充表变量。如果表变量中的小部件编号在产品表中不存在，我将返回该编号并将其标记为缺失。

然而;我们的 DBA 非常反对游标，而且我知道 WHILE 循环需要大量开销。另外，我不确定如何按商店执行此操作。

有谁知道如何以基于集合的方式执行此操作？

小部件本身有编号，因此当您出售小部件 #1 时，您出售的下一个小部件应该是小部件 #2。然而;有时，这种情况不会发生，小部件 #3 已售出 - 小部件 #2 丢失且不在数据库中。我需要在报告中确定小部件 #1 和 #3 已售出，而 #2 丢失。

Answer 1

更新答案

创建表和工作解决方案的示例代码如下。基本上，您会执行 2 个 EXISTS 检查 - 查看是否有比当前数字大的数字，并且没有比当前数字大 1 的数字。

DECLARE @t Table (Store int, Product varchar(100), number int)
INSERT INTO @t
VALUES
(1, 'Widget',   100),
(1, 'Widget',   101),
(1, 'Widget',   102),
(1, 'Widget',   104),
(2, 'Widget',   201),
(2, 'Widget',   202),
(2, 'Widget',   203)

SELECT Store, Product, t.Number+1 as Missing
FROM @t t
WHERE EXISTS (SELECT 1 FROM @t t2
              WHERE t2.Store = t.Store
              AND t2.product = t.product
              AND t2.number > t.number)
AND Not Exists (SELECT 1 FROM @t t3
                WHERE t3.Store = t.store
                AND t3.product = t.product
                AND t3.number = t.number + 1)

Answer 2

创建一个数字表并在其中填充比您需要的更多的数字。
然后您可以使用基于集合的查询来获取结果

Create table #temp (product varchar(15), id int)
insert into #temp
values ('test', 1), ('test', 3),('test', 4),('test2', 6),('test2', 2),('test2', 10),('test3', 10),('test3', 9),('test3',7),('test4', 1),('test4', 2),('test4', 3)

create table #product (product varchar (15))
insert into #product 
values ('test'),('test2'),('test3'),('test4'), ('test5')
create table #num (number int)

insert into #num
values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)

select n.number,p.product from #num n
cross join #product p 
left join (
select product, max(id)as maxid from #temp group by product)a
on a.product = p.product 
left join #temp t on n.number = t.id and t.product = p.product
where  n.number <=a.maxid and t.id is null
order by p.product