問題描述

我有下面的 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 將為零.我是否需要創(chuàng)建一個包含 1 到 10 個數(shù)字的臨時表?

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 表只是數(shù)字表.有很多方法可以使用 子查詢:

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

• 遞歸 cte
• ROW_NUMBER() 來自包含許多值的系統(tǒng)表(此處使用)
• 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;

這篇關(guān)于TSQL:我如何檢測和插入丟失的記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！