TSQL:我如何检测和插入丢失的记录

2021-09-10 00:00:00 sql tsql sql-server

我有下面的 T-SQL 表.

I have T-SQL Table below.

 ID   Cost    MaxCost
 -------------------------------
 2    200     300
 3    400     1000
 6    20      100

上表必须有 10 行 ID 为 1 到 10.所以它缺少 7 行.如何插入具有正确 ID 的缺失行.成本&缺失行的 maxcost 将为零.我是否需要创建一个包含 1 到 10 个数字的临时表?

The above table must have 10 rows with IDs 1 to 10. So its missing 7 rows. How do i insert missing rows with proper ID. The cost & maxcost for missing rows will be zero. Do i need to create a temp table that holds 1 to 10 numbers?

推荐答案

不需要临时表，简单的tally派生表和LEFT OUTER JOIN就足够了:

No need for temp table, simple tally derived table and LEFT OUTER JOIN are sufficient:

CREATE TABLE #tab(ID INT, Cost INT, MaxCost INT);

INSERT INTO #tab(ID, Cost, MaxCost)
VALUES (2, 200,300),(3, 400, 1000) ,(6, 20, 100);

DECLARE @range_start INT = 1
       ,@range_end INT = 10;

;WITH tally AS
(
  SELECT TOP 1000 r = ROW_NUMBER() OVER (ORDER BY name)
  FROM master..spt_values
)
INSERT INTO #tab(id, Cost, MaxCost)
SELECT t.r, 0, 0
FROM tally t
LEFT JOIN #tab c
  ON t.r = c.ID
WHERE t.r BETWEEN @range_start AND @range_end
  AND c.ID IS NULL;

SELECT *
FROM #tab
ORDER BY ID;

LiveDemo

Tally 表只是数字表.有很多方法可以使用 子查询:

Tally table is simply number table. There are many ways to achieve it with subquery:

  • 递归 cte
  • ROW_NUMBER() 来自包含许多值的系统表(此处使用)
  • UNION ALL 和 CROSS JOIN
  • VALUES(...)
  • 使用 OPENJSON (SQL Server 2016+)
  • ...
  • recursive cte
  • ROW_NUMBER() from system table that holds many values (used here)
  • UNION ALL and CROSS JOIN
  • VALUES(...)
  • using OPENJSON (SQL Server 2016+)
  • ...

TOP 1000 将仅生成 1000 条记录，如果您知道需要更多记录可以使用:

The TOP 1000 will generate only 1000 records if you know that you need more you can use:

SELECT TOP 1000000 r = ROW_NUMBER() OVER (ORDER BY (SELECT 1))
FROM master..spt_values c
CROSS JOIN master..spt_values c2;

相关文章