I've found a few questions that deal with this problem, and it appears that MySQL doesn't allow it. That's fine, I don't have to have a subquery in the FROM clause. However, I don't know how to get around it. Here's my setup:

我发现了一些关于这个问题的问题，MySQL似乎不允许这样做。很好，我不需要在FROM子句中有子查询。然而，我不知道如何避开它。这是我的设置:

I have a metrics table that has 3 columns I want: ControllerID, TimeStamp, and State. Basically, a data gathering engine contacts each controller in the database every 5 minutes and sticks an entry in the metrics table. The table has those three columns, plus a MetricsID that I don't care about. Maybe there is a better way to store those metrics, but I don't know it. Regardless, I want a view that takes the most recent TimeStamp for each of the different ControllerIDs and grabs the TimeStamp, ControllerID, and State. So if there are 4 controllers, the view should always have 4 rows, each with a different controller, along with its most recent state.

我有一个度量表，它有3个列:ControllerID、TimeStamp和State。基本上，数据收集引擎每隔5分钟就与数据库中的每个控制器联系一次，并在metrics表中插入一个条目。这个表有这三列，加上一个我不关心的矩阵sid。也许有更好的方法来存储这些度量，但是我不知道。无论如何，我希望有一个视图，它为每个不同的ControllerIDs获取最新的时间戳，并获取时间戳、ControllerID和状态。如果有4个控制器，视图应该有4行，每一行都有不同的控制器，以及最近的状态。

I've been able to create a query that gets what I want, but it relies on a subquery in the FROM clause, something that isn't allowed in a view. Here is what I have so far:

我可以创建一个查询来获取我想要的，但是它依赖于FROM子句中的子查询，这在视图中是不允许的。以下是我目前所拥有的:

SELECT *
FROM
    (SELECT 
    ControllerID, TimeStamp, State
    FROM Metrics
    ORDER BY TimeStamp DESC)
AS t
GROUP BY ControllerID;

Like I said, this works great. But I can't use it in a view. I've tried using the max() function, but as per here: SQL: Any straightforward way to order results FIRST, THEN group by another column? if I want any additional columns besides the GROUP BY and ORDER BY columns, max() doesn't work. I've confirmed this limitation, it doesn't work.

就像我说的，这很有效。但我不能在视图中使用它。我已经尝试过使用max()函数，但是如这里所示:SQL:有什么简单的方法可以先对结果排序，然后再对另一列进行分组吗?如果我想要除GROUP BY和ORDER BY列之外的任何其他列，max()不起作用。我已经确认了这个限制，它不起作用。

I've also tried to alter the metrics table to order by TimeStamp. That doesn't work either; the wrong rows are kept.

我还尝试通过时间戳将metrics表更改为order。这并不工作;错误的行被保留。

Edit: Here is the SHOW CREATE TABLE of the Metrics table I am pulling from:

编辑:这是我正在提取的度量表的SHOW CREATE表格:

 CREATE TABLE Metrics (
  MetricsID int(11) NOT NULL AUTO_INCREMENT,
  ControllerID int(11) NOT NULL,
  TimeStamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  State tinyint(4) NOT NULL,
  PRIMARY KEY (MetricsID),
  KEY makeItFast (ControllerID,MetricsID),
  KEY fast (ControllerID,TimeStamp),
  KEY fast2 (MetricsID),
  KEY MetricsID (MetricsID),
  KEY TimeStamp (TimeStamp)
) ENGINE=InnoDB AUTO_INCREMENT=8958 DEFAULT CHARSET=latin1

2 个解决方案

SELECT ControllerID, TimeStamp, State
FROM Metrics m
WHERE NOT EXISTS (SELECT 1
                  FROM Metrics m2
                  WHERE m2.ControllerId = m.ControllerId and m2.Timestamp > m.TimeStamp
                 );

SELECT ControllerId, TimeStamp, State
  FROM Metrics m
  WHERE MetricsID IN (
              SELECT MAX(MetricsID) MetricsID
                FROM Metrics
               GROUP BY ControllerID) 
  ORDER BY ControllerID