使用最近行的值進行更新

我的表如下所示：

---------------------------------------
**id** | **姓名** | **家名** | **豪斯諾** 
-----------------------------------------
1 | 約翰 | 約翰之家 | 2/22
-----------------------------------------
2 | 雷切爾 | 約翰之家 | 2/22
-----------------------------------------
3 | 喜悅 | 約翰之家 | 
-----------------------------------------
4 | 亞歷克斯 | 亞歷克斯的房子 | 
-----------------------------------------
5 | 雷切爾 | 亞歷克斯的房子 | 3/22
-----------------------------------------
6 | 本 | 亞歷克斯的房子 | 3/22
-----------------------------------------

我想houseno通過與具有相同housename. 使用上面的範例，對於 id 3，houseno應該是 2/22（houseno約翰的房子），對於 id 4，它應該是3/22。該id列是主鍵並且不是連續的。

這兩種方法中的哪一種——min(id) > id或者id < id order by id desc limit 1——可以很好地檢索前一行？

更新

抱歉讓問題不清楚，DISTINCT不能使用，因為有可能會再次出現該戶名。該表可以像下面的那個……

---------------------------------------
**id** | **姓名** | **家名** | **豪斯諾** 
-----------------------------------------
1 | 約翰 | 約翰之家 | 2/22
-----------------------------------------
2 | 雷切爾 | 約翰之家 | 2/22
-----------------------------------------
3 | 喜悅 | 約翰之家 | 
-----------------------------------------
4 | 亞歷克斯 | 亞歷克斯的房子 | 
-----------------------------------------
5 | 雷切爾 | 亞歷克斯的房子 | 3/22
-----------------------------------------
6 | 本 | 亞歷克斯的房子 | 3/22
-----------------------------------------
7 | 約翰·多伊 | 約翰之家 | 
-----------------------------------------
8 | 貝蒂·多伊 | 約翰之家 | 4/22
-----------------------------------------
9 | 蘇菲·多伊 | 約翰之家 | 4/22 
-----------------------------------------

有沒有辦法將沒有 houseno 的行與 previous 和 next 分組？

housename如果我們可以安全地假設具有相同的行將具有相同的填充houseno位置，則以下方法將按預期工作。houseno

作為第一步，您需要獲取所有具有值的不同housename/houseno對的列表houseno。如果我上面的假設是正確的，你會得到一個所有housename值都是唯一的結果集，並且每個值都有一個對應houseno的——一種參考表：

SELECT DISTINCT
 housename,
 houseno
FROM
 YourTable
WHERE
 houseno <> ''

使用上述作為派生表在 UPDATE 語句中將其連接回原始表，以便您可以從派生表中填充缺失值：

UPDATE
 YourTable AS t
 INNER JOIN
 (
   SELECT DISTINCT
     housename,
     houseno
   FROM
     YourTable
   WHERE
     houseno <> ''
 ) AS ref ON t.housename = ref.housename
SET
 t.houseno = ref.houseno
WHERE
 t.houseno = '' OR t.houseno IS NULL
;

請注意，與其像這樣“修復”您的表，不如通過創建一個包含列的實際引用表來對其進行規範化，housename然後houseno您可以在需要檢索時加入您的數據表houseno。因此，houseno將不再需要原始表中的列。

這可以通過儲存的常式來完成：

BEGIN 
 SET @prevname = ''
   , @prevno   = ''
 ;

 UPDATE table AS w
    SET w.houseno = IF( @prevname = @prevname := w.housename, 
                        @prevno, 
                        @prevno := w.houseno 
                      )
  ORDER BY w.housename ASC
         , w.houseno DESC
 ;
END

這裡有三個技巧。

首先是User-Defined Variables（UDV）在行進行中是持久的。一旦分配，他們將儲存該值，直到重新分配。

其次，在 UDV 的上下文中，具有不同優先級的比較=和分配之間存在差異。:=這裡

IF( @prevname = @prevname := w.housename,

表示： IF 先前分配的值@prevname等於新分配的@prevnameTHEN …

第三是行按 ASC-DESC 順序排列。housename如果分配了相同的任何行houseno，它將首先進行並將其值傳播到所有後續行。如果您housename錯誤地將兩個不同的值分配給具有相同的任意兩行，則按字母順序排列的較大值將在整個組中傳播。

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