Question

我有一个表格,载有各种服务器的登录记录。我需要就每个<代码>idServer的最新(逐时间)登录形成看法。

mysql> describe serverLog;
+----------+-----------+------+-----+-------------------+----------------+
| Field    | Type      | Null | Key | Default           | Extra          |
+----------+-----------+------+-----+-------------------+----------------+
| idLog    | int(11)   | NO   | PRI | NULL              | auto_increment | 
| idServer | int(11)   | NO   | MUL | NULL              |                | 
| time     | timestamp | NO   |     | CURRENT_TIMESTAMP |                | 
| text     | text      | NO   |     | NULL              |                | 
+----------+-----------+------+-----+-------------------+----------------+

mysql> select * from serverLog;
+-------+----------+---------------------+------------+
| idLog | idServer | time                | text       |
+-------+----------+---------------------+------------+
|     1 |        1 | 2009-12-01 15:50:27 | log line 2 | 
|     2 |        1 | 2009-12-01 15:50:32 | log line 1 | 
|     3 |        3 | 2009-12-01 15:51:43 | log line 3 | 
|     4 |        1 | 2009-12-01 10:20:30 | log line 0 | 
+-------+----------+---------------------+------------+

造成这种困难的原因(对我而言)是:

Entries for earlier dates/times may be inserted later, so I can t just rely on idLog.
timestamps are not unique, so I need to use idLog as a tiebreaker for "latest".

我可以取得我希望使用分局的结果,但我可以提出分局。此外,我听到MySQL的弹 sub。

mysql> SELECT * FROM (
    SELECT * FROM serverLog ORDER BY time DESC, idLog DESC
    ) q GROUP BY idServer;
+-------+----------+---------------------+------------+
| idLog | idServer | time                | text       |
+-------+----------+---------------------+------------+
|     2 |        1 | 2009-12-01 15:50:32 | log line 1 | 
|     3 |        3 | 2009-12-01 15:51:43 | log line 3 | 
+-------+----------+---------------------+------------+

我的看法是正确的。

Answer 1

我建议使用:

CREATE OR REPLACE VIEW vw_your_view AS
  SELECT t.*
    FROM SERVERLOG t
    JOIN (SELECT sl.idserver,
                 MAX(sl.time)  max_time 
            FROM SERVERLOG sl
        GROUP BY sl.idserver) x ON x.idserver = t.idserver
                               AND x.max_time = t.time

从未界定过<代码>。载于国际妇女论坛的“,因为你每次使用该观点时都无需具体注明的命令。

友情链接