TSQL:我如何检测和插入丢失的记录
我有下面的 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
andCROSS 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;
相关文章