MySQL JOIN 兩個表並獲得最新結果

我想加入兩個表並在一個表中從這兩個表中的每一個中獲取最新結果。我也有點擔心速度，因為表格增長得有點快。每天接近 60-70k 條記錄。稍後我將進行分區，但這是另一個問題。現在我有一個包含devices資訊的主表。

+--------+-----------+---------+
|     id |    Name   | type    |
+--------+-----------+---------+
|      1 | Oh        | A       |
|      2 | This      | A       |
|      3 | Is        | B       |
|      4 | Hard      | A       |
+--------+-----------+---------+

根據類型，他們在不同的表中有一些數據類型 A 是

+--------+-----------+------------------+---------+---------+
|     id | device_id |   stats_time     | status  |  noise  |
+--------+-----------+------------------+---------+---------+
|      1 | 1         | 2012-10-23 07:50 | foo     |   10    |
|      2 | 1         | 2012-10-23 16:59 | bar     |   12    |
|      3 | 2         | 2012-10-23 15:11 | bar     |   0     |
|      4 | 4         | 2012-10-23 23:23 | foo     |   25    |
+--------+-----------+------------------+---------+---------+

B型是

+--------+-----------+------------------+---------+---------+
|     id | device_id |   stats_time     | status  |  signal |
+--------+-----------+------------------+---------+---------+
|      1 | 3         | 2012-10-23 04:50 | foo     |  1000   |
|      2 | 3         | 2012-10-23 05:59 | bar     |  450    |
|      3 | 3         | 2012-10-23 09:11 | bar     |  980    |
|      4 | 3         | 2012-10-23 10:23 | foo     |   0     |
+--------+-----------+------------------+---------+---------+

我一直在努力尋找一個查詢，最終得到這樣的結果

+--------+-----------+------------------+---------+---------+---------+
|     id | device_id |   stats_time     | status  |  signal |   noise |
+--------+-----------+------------------+---------+---------+---------+
|      1 | 1         | 2012-10-23 16:59 | bar     |  12     |         |
|      2 | 2         | 2012-10-23 15:11 | bar     |  0      |         |
|      3 | 3         | 2012-10-23 10:23 | foo     |         |    0    |
|      4 | 4         | 2012-10-23 23:23 | foo     |  25     |         |
+--------+-----------+------------------+---------+---------+---------+

使用下面的查詢不好，因為我得到兩列stats_time

SELECT devices.id AS id, A.stats_time , B.stats_time
FROM devices 
LEFT JOIN A ON devices.id = A.device_id 
LEFT JOIN B ON devices.id = B.device_id 
GROUP BY devices.id

在我最終為設備類型使用不同的表之前，我曾經通過以下方式獲得結果，但最終變得非常緩慢

SELECT *
FROM (
   SELECT *
   FROM A
   ORDER BY stats_time DESC, id ASC
) AS d
RIGHT JOIN devices ON A.device_id = devices.id
GROUP BY devices.id

我認為它分為兩個步驟：

1. 為每個設備建構僅包含最新信號（或雜訊）的表
2. JOIN或UNION兩張桌子。

步驟 1 是groupwise max的變體：

SELECT  device_id, stats_time, status, noise -- The desired columns
   FROM  ( SELECT  @prev := '' ) init
   JOIN  ( SELECT
               device_id != @prev AS first, -- `device_id` is the 'GROUP BY'
               @prev := device_id,          -- the 'GROUP BY'
               device_id, stats_time, status, noise -- Also the desired columns
           FROM  TableA -- The table
           ORDER BY  device_id  DESC, -- The 'GROUP BY'
                     stats_time DESC  -- to get latest
     ) x
   WHERE  first; 

這可能對性能有益：

INDEX(device_id, stats_time)

TableB和同上signal。手動執行它們，看看我是否正確。

您的範例沒有顯示兩者signal和都noise存在的情況device_id。我會假設情況確實如此，因此UNION：

第2步：

SELECT device_id, stats_time, status, signal, noise
   FROM
   ( SELECT device_id, stats_time, status, signal, '' AS noise
       ... (the rest of the signal query)
   )
   UNION ALL
   ( SELECT device_id, stats_time, status, '' AS signal, noise
       ... (the rest of the noise query)
   );

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