如何選擇具有一種狀態的最新記錄，而其他狀態不存在以後的記錄？

我想選擇具有給定狀態的最後一條記錄（按日期時間），對於每個給定的“小元件”，不存在任何其他狀態的後續記錄。

widget     state    timestamp
------     -----    ---------
foo        up       2011-02-11 12:34:56
foo        down     2011-02-10 10:01:10
bar        down     2011-02-09 09:00:20
bar        up       2011-02-08 08:11:20
baz        down     2011-02-07 07:04:18
baz        up       2011-02-06 06:05:18

以及更多這樣的記錄 - 每個小元件超過兩個，並且超過顯示的兩個狀態。時間戳沒有順序（它們的記錄被插入而不考慮時間戳）。唯一鍵是在外部（由另一個應用程序）生成的 ID。

例如，我想選擇 state = “down” 的記錄，結果應該包括 “bar” 和 “baz” 的記錄，但不包括 “foo” 的記錄，因為 “foo” 的後面狀態不是 “下”。

我正在使用自然自連接來為不同的查詢選擇一個小元件的最新事件，並認為也許以某種方式建立在這個之上會適用於這個。

SELECT widget, MAX(`timestamp`) AS ts
FROM tableX AS t
WHERE state = 'down'
GROUP BY widget
HAVING NOT EXISTS
      ( SELECT *
        FROM tableX AS tt
        WHERE tt.widget = t.widget
          AND tt.state <> 'down'
          AND tt.`timestamp` > MAX(t.`timestamp`)
      ) ;

我認為您需要兩個索引，一個打開(widget, state, timestamp)，一個打開(widget, timestamp, state)以提高效率。

---

這也將起作用，並且只需要一個索引， on (widget, timestamp, state)：

SELECT t.widget, t.`timestamp`
FROM 
       tableX AS t
   JOIN
       ( SELECT widget, MAX(`timestamp`) AS ts
         FROM tableX
         GROUP BY widget
       ) AS tm
           ON  tm.widget = t.widget
           AND tm.ts = t.`timestamp`
WHERE t.state = 'down' ;

在 SQL-Fiddle 測試過：test

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