问题描述
我有下面的 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;