Sql-Server

用其他值更新空值

  • September 21, 2021

我是 SQL Server 世界的新手。在下面的範例中，我有兩家公司：

  • 建築模組
  • 紅水泥

每個公司只有一個條目具有列的值power1, power2, power3。

我需要更新表格，以便每個公司的所有列都有一個值。換句話說，我需要更新所有空白（null）值。

虛構數據範例：

Create Table #Rentarious
(
 c1 varchar(200)
 ,yryryryr datetime
 ,power1 varchar(200)
 ,power2 varchar(200)
 ,power3 varchar(200)
)   

Insert Into #Rentarious VALUES
('Building Blocks','2016','Red','Blue','Green')
,('Red Cement', '2012', 'Pink','Purple','Orange')

Insert Into #Rentarious(c1, yryryryr) VALUES 
('Building Blocks', '2012')
,('Building Blocks', '2013')
,('Building Blocks', '2014')
,('Red Cement', '2016')
,('Red Cement', '2011')

題

編寫更新語句以將列的空值更新為表power1, power2, power3中已列出的值的語法是什麼？

您可以SET使用逗號分隔多個欄位。在這種情況下，您只需要根據一些獨特的模式指定記錄c1 and yryryryr。

UPDATE #Rentarious
SET   power1 = 'Red'
   , power2 = 'Blue'
   , power3 = 'Green'
WHERE   c1       = 'Build Blocks'
   AND yryryryr = '2012';

編寫更新語句以power1, power2, power3使用表中已列出的值更新欄位的空值的語法是什麼？

我認為這意味著每家公司的空值都應該根據從一個填充條目中獲取的值來填充，正如您在文章中其他地方所說的那樣，每家公司都有。因此，在虛擬碼中，您的 UPDATE 語句需要如下所示：

UPDATE
 #Rentarious
SET
 power1 = power1 from the same company’s populated row,
 power2 = power2 from the same company’s populated row,
 power3 = power3 from the same company’s populated row
WHERE power1 IS NULL
 AND power2 IS NULL
 AND power3 IS NULL
;

實現上述模式的一種方法是使用相關子查詢：

UPDATE
 #Rentarious
SET
 power1 = (SELECT power1 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power1 IS NOT NULL),
 power2 = (SELECT power2 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power2 IS NOT NULL),
 power3 = (SELECT power3 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power3 IS NOT NULL)
WHERE power1 IS NULL
 AND power2 IS NULL
 AND power3 IS NULL
;

雖然這樣會起作用，但這樣的更新可能不是很有效，因為同一個表會被多接觸 3 次才能獲得源值。由於您知道非空值儲存在每家公司的一行中，因此您可以通過使用派生表和專有的“使用連接更新”語法在表上的一次額外傳遞中獲取它們：

UPDATE
 #Rentarious
SET
 power1 = sub.power1,
 power2 = sub.power2,
 power3 = sub.power3
FROM
 (
   SELECT
     c1,
     power1,
     power2,
     power3
   FROM
     #Rentarious
   WHERE power1 IS NOT NULL
     AND power2 IS NOT NULL
     AND power3 IS NOT NULL
 ) AS sub
WHERE #Rentarious.c1 = sub.c1
 AND #Rentarious.power1 IS NULL
 AND #Rentarious.power2 IS NULL
 AND #Rentarious.power3 IS NULL
;

您還可以重寫它以使用顯式 JOIN 語法：

UPDATE
 tgt
SET
 power1 = src.power1,
 power2 = src.power2,
 power3 = src.power3
FROM
 #Rentarious AS tgt
 INNER JOIN
 (
   SELECT
     c1,
     power1,
     power2,
     power3
   FROM
     #Rentarious
   WHERE power1 IS NOT NULL
     AND power2 IS NOT NULL
     AND power3 IS NOT NULL
 ) AS sub
 ON tgt.c1 = sub.c1
WHERE tgt.power1 IS NULL
 AND tgt.power2 IS NULL
 AND tgt.power3 IS NULL
;

如您所見，兩種變體中只有一個子查詢，它提供所有三個值來填充其他行。

但請注意，也可以在不進行任何額外掃描的情況下解決此問題。首先，如果這是一個 SELECT 語句，您可以使用視窗聚合函式在每一行中返回填充行的值，如下所示：

SELECT
 c1,
 yryryryr,
 power1,
 power2,
 power3,
 populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
 populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
 populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
FROM
 #Rentarious
;

MAX函式在這種情況下工作，因為它只返回指定集合中非空值的最大值。在您的情況下，在這三種情況下，每個分區只有一個非空值c1，因此該函式將返回該值。這將是您問題中範例的查詢結果：

c1               yryryryr    power1  power2  power3  populatedPower1  populatedPower2  populatedPower3
---------------  ----------  ------  ------  ------  ---------------  ---------------  ---------------
Building Blocks  2012-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2013-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2014-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2016-01-01  Red     Blue    Green   Red              Blue             Green
Red Cement       2012-01-01  Pink    Purple  Orange  Pink             Purple           Orange
Red Cement       2016-01-01  NULL    NULL    NULL    Pink             Purple           Orange
Red Cement       2011-01-01  NULL    NULL    NULL    Pink             Purple           Orange

所以唯一剩下的就是

SET
 power1 = populatedPower1,
 power2 = populatedPower2,
 power3 = populatedPower3

在 SQL Server 中確實可以這樣做，因為上述 SELECT 查詢的結果可以用作您的 UPDATE 語句的目標。您可以將其用作派生表：

UPDATE
 tgt
SET
 power1 = populatedPower1,
 power2 = populatedPower2,
 power3 = populatedPower3
FROM
 (
   SELECT
     c1,
     yryryryr,
     power1,
     power2,
     power3,
     populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
     populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
     populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
   FROM
     #Rentarious
 ) AS tgt
WHERE power1 IS NULL
 AND power2 IS NULL
 AND power3 IS NULL
;

或將其實現為 CTE（公用表表達式）並使用 CTE 的別名作為目標：

WITH tgt AS
 (
   SELECT
     c1,
     yryryryr,
     power1,
     power2,
     power3,
     populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
     populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
     populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
   FROM
     #Rentarious
 )
UPDATE
 tgt
SET
 power1 = populatedPower1,
 power2 = populatedPower2,
 power3 = populatedPower3
WHERE power1 IS NULL
 AND power2 IS NULL
 AND power3 IS NULL
;

兩者都可以很好地工作，並導致所有空值都替換為相應的值，即：

c1               yryryryr    power1  power2  power3
---------------  ----------  ------  ------  ------
Building Blocks  2016-01-01  Red     Blue    Green
Red Cement       2012-01-01  Pink    Purple  Orange
Building Blocks  2012-01-01  NULL    NULL    NULL
Building Blocks  2013-01-01  NULL    NULL    NULL
Building Blocks  2014-01-01  NULL    NULL    NULL
Red Cement       2016-01-01  NULL    NULL    NULL
Red Cement       2011-01-01  NULL    NULL    NULL

對此：

c1               yryryryr    power1  power2  power3
---------------  ----------  ------  ------  ------
Building Blocks  2016-01-01  Red     Blue    Green
Red Cement       2012-01-01  Pink    Purple  Orange
Building Blocks  2012-01-01  Red     Blue    Green
Building Blocks  2013-01-01  Red     Blue    Green
Building Blocks  2014-01-01  Red     Blue    Green
Red Cement       2016-01-01  Pink    Purple  Orange
Red Cement       2011-01-01  Pink    Purple  Orange

引用自：https://dba.stackexchange.com/questions/140613

相關問答

Sql-Server

執行 DELETE 儲存過程，但發現 WHILE 循環中的性能隨著迭代次數的增加而下降

  • March 23, 2022
檢視問答
Sql-Server

對象“SPROC”、數據庫“DATABASE”、模式“dbo”的 EXECUTE 權限被拒絕

  • March 10, 2022
檢視問答
Sql-Server

檢測兩個時間段是否重疊

  • February 25, 2022
檢視問答
Sql-Server

試圖找出值最後一次改變的時間

  • February 17, 2022
檢視問答
Sql-Server

如何檢查非ASCII字元

  • December 17, 2021
檢視問答
Sql-Server

INSERT 語句中的行值表達式的數量超過了允許的最大行值數 1000

  • October 19, 2021
檢視問答