Mysql

MySQL 根據另一個表中的最後一個數據更新一個表，同時按條件分組

  • March 20, 2015

我需要一個查詢來更新基於另一個表的一系列欄位

表lp_plates_backup

|       uid | brand    | model    |
|         1 |          |          |
|         a | Old      | Error    |
|       ... | ...      | ...      |

表lp_pictures_backup

| plate_uid | brand    | model    | date_validated      |
|         1 | Fiat     | Panda    | 2014-10-11 10:03:20 | < Last one
|         1 | BMW      | 7-Series | 2014-08-04 11:21:18 |
|         1 | BMW      | 7-Series | 2014-07-28 19:14:02 |
|         1 | Mercedes | S-Class  | 2014-06-12 08:54:57 |
|         a | Tesla    | Model S  | 2014-12-17 11:00:00 | < Last one
|         a | BMW      | 3-Series | 2014-11-07 14:34:11 |

我需要根據表lp_plates_backup的最後一個date_validated條目更新lp_plates_backup的列****品牌和型號，而lp_plates_backup.uid與每個lp_plates_backup.uid匹配lp_pictures_backup.plate_uid但僅在以下情況下：

  • lp_pictures_backup.brand不為空
  • lp_plates_backup.brand不同於lp_pictures_backup.brand或者lp_plates_backup.model不同於lp_pictures_backup.model

結果應顯示（lp_plates_backup）：

|       uid | brand    | model    |
|         1 | Fiat     | Panda    |
|         a | Tesla    | Model S  |

我已經閱讀了這些文章並進行了很多測試，但沒有得到預期的結果：

這是我嘗試的最後一個查詢：

UPDATE
   lp_plates_backup AS t
   LEFT JOIN (
       SELECT
           plate_uid, brand, model, date_validated
       FROM
           lp_pictures_backup as parent
       WHERE
           brand <> '' AND
           date_validated = (SELECT MAX(date_validated)
                FROM lp_pictures_backup as t2
                WHERE t2.plate_uid = parent.plate_uid)
       GROUP BY
           plate_uid
   ORDER BY
       date_validated DESC

) AS m ON
   m.plate_uid = t.uid
SET
   t.brand = m.brand,
   t.model = m.model
WHERE
   t.brand <> m.brand
   OR
   t.model <> m.model

我想我有太多的頭腦風暴，並且對製作無用的過於復雜的程式碼感到困惑。

現場有高手嗎？

感謝您的幫助。

正確的查詢是：

UPDATE
   lp_plates_backup AS t
   INNER JOIN  (
       SELECT
           plate_uid, brand, model, date_validated
       FROM
           lp_pictures_backup as parent
       WHERE
           brand <> '' AND
           date_validated = (
                SELECT MAX(date_validated)
                FROM lp_pictures_backup as t2
                WHERE t2.plate_uid = parent.plate_uid
                GROUP BY
                    plate_uid)
) AS m ON
   m.plate_uid = t.uid
SET
   t.brand = m.brand,
   t.model = m.model
WHERE
   t.brand <> m.brand
   OR
   t.model <> m.model;

只是一些小小的解釋。您需要INNER JOIN，因為您必須更新行lp_plates_backup僅當plate_uid存在於lp_plates_backup. ORDER BY沒用，因為您正在選擇所有行，順序並不重要。

您需要date_validated按 分組的最大行數plate_uid，選擇 MAX(date_validate) 必須plate_uid通過該date_validated欄位僅區分一行。所以你需要添加 group by 來選擇 max(..)。

查詢select plate_uid返回一行，plate_uid因此您無需在此處聚合。單行的條件已經建構到select max子查詢中。

我希望你沒有被我弄糊塗:-)

更新

date_validate如果對 ( plate_uid, date_validated) 是唯一的，則前面的效果很好。如果你有這樣的數據：

| plate_uid | brand    | model    | date_validated      |
|         1 | Fiat     | Panda    | 2014-10-11 10:03:20 | 
|         1 | BMW      | 7-Series | 2014-10-11 10:03:20 |   <- changed data
|         1 | BMW      | 7-Series | 2014-07-28 19:14:02 |
|         1 | Mercedes | S-Class  | 2014-06-12 08:54:57 |   
|         a | Tesla    | Model S  | 2014-12-17 11:00:00 | 
|         a | BMW      | 3-Series | 2014-11-07 14:34:11 |

以下查詢返回plate_uid1 的前兩行。

SELECT plate_uid, brand, model, date_validated
FROM lp_pictures_backup as parent
WHERE
     brand <> '' AND
     date_validated = (
                SELECT MAX(date_validated)
                FROM lp_pictures_backup as t2
                WHERE t2.plate_uid = parent.plate_uid
                GROUP BY plate_uid)

更新將從第一行或第二行中選擇值。我想到了以下替代方案：

  1. 使用其他一些lp_pictures_backup欄位在具有相同 date_validated 的行之間進行選擇。
  2. 對錶強制執行唯一約束。即alter table lp_pictures_backup add unique index (plate_id, date_validated)。拒絕無效數據。
  3. 檢測有效lp_pictures_backup對(plate_uid, date_validated). 僅使用有效對進行更新lp_plates_backup，檢查無效對並更正它們。

也許還有更多的選擇。我更喜歡對數據實施約束，以便獲得更好的數據。我擴展了第三種選擇。只需創建一個視圖來定義有效對(plate_uid, date_validated)是什麼：

CREATE VIEW lp_pictures_backup_valid as
SELECT plate_uid, date_validated
FROM lp_pictures_backup as parent
WHERE date_validated = (
       select max(date_validated) 
       from lp_pictures_backup t2 
       where t2.plate_uid =  parent.plate_uid GROUP BY plate_uid
     )
group by plate_uid, date_validated
having count(*) = 1;                    <-- you can change this to make an invalid row

(plate_uid, date_validated)僅當存在唯一的 date_validated 值時，有效對才具有最大 date_validated 值。

我重寫了更新語句以僅考慮有效對：

UPDATE
   lp_plates_backup AS t
   INNER JOIN  (
       -- modification start
       SELECT p.plate_uid, p.brand, p.model, p.date_validated
       FROM lp_pictures_backup as p
            INNER JOIN lp_pictures_backup_valid valid 
                       ON p.plate_uid = valid.plate_uid and 
                          p.date_validated = valid.date_validated
       WHERE p.brand <> '')
       -- modification end
   ) 
   AS m ON m.plate_uid = t.uid
SET
   t.brand = m.brand,
   t.model = m.model
WHERE
   t.brand <> m.brand
   OR
   t.model <> m.model;

希望這是有道理的。

更新：2014-03-20

在第一種情況下：

Blockquote 1. 使用其他一些lp_pictures_backup欄位在具有相同 date_validated 的行之間進行選擇。

我假設你的日期是這樣的：

|id| plate_uid | brand    | model    | date_validated      |
|4 |         1 | Fiat     | Panda    | 2014-10-11 10:03:20 | 
|3 |         1 | BMW      | 7-Series | 2014-10-11 10:03:20 |   
|2 |         1 | BMW      | 7-Series | 2014-07-28 19:14:02 |
|1 |         1 | Mercedes | S-Class  | 2014-06-12 08:54:57 |    
|2 |         a | Tesla    | Model S  | 2014-12-17 11:00:00 | 
|1 |         a | BMW      | 3-Series | 2014-11-07 14:34:11 |

你可以試試這個：

UPDATE
   lp_plates_backup AS t
   INNER JOIN  (
       SELECT t1.plate_uid, t1.brand, t1.model, t1.date_validated
       FROM lp_pictures_backup as t1,
         (SELECT t2.plate_uid, MAX(id) as id,  MAX(date_validated) as dv
          FROM lp_pictures_backup as t2
          GROUP BY t2.plate_uid) as t3                 
       WHERE t1.brand <> '' AND
             t1.plate_uid = t3.plate_uid AND
             t1.date_validated = t3.dv AND
             t1.id = t3.id
) AS m ON
   m.plate_uid = t.uid
SET
   t.brand = m.brand,
   t.model = m.model
WHERE
   t.brand <> m.brand
   OR
   t.model <> m.model;

這部分提取了用於選擇要更新的行的欄位：

...
(SELECT t2.plate_uid, MAX(id) as id,  MAX(date_validated) as dv
 FROM lp_pictures_backup as t2
 GROUP BY t2.plate_uid) as t3 
...

因此它假設 date_validated 和 id 之間存在相關性：在增加的日期對應於增加的 id。

希望它有所幫助。

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

相關問答

Mysql

如何使用 GROUP BY 更快地更新？

  • May 27, 2021
檢視問答
Mysql

按單個成員選擇組並對其他值求和

  • August 31, 2022
檢視問答
Mysql

如何獲得 3 個子計數查詢的總和並使用屬性對它們進行分組

  • March 11, 2022
檢視問答
Mysql

通過自我 JOIN 和 GROUP BY 更新表

  • December 29, 2021
檢視問答
Mysql

MySQL 多個子查詢 - 排序緩慢

  • November 6, 2021
檢視問答
Mysql

使用 WHERE 條件為每個 GROUP 選擇 n 行

  • September 12, 2021
檢視問答