我发现了一些可以解决此问题的问题,而且MySQL似乎不允许这样做.很好,我在FROM子句中不必有子查询.但是,我不知道该如何解决.这是我的设置：
我有一个指标表,该表具有我想要的3列：ControllerID,TimeStamp和State.基本上,数据收集引擎每5分钟与数据库中的每个控制器联系一次,并将条目粘贴到指标表中.该表包含这三列,外加我不关心的MetricsID.也许有更好的方法来存储这些指标,但是我不知道.
无论如何,我都希望一个视图为每个不同的ControllerID获取最新的时间戳,并获取时间戳,ControllerID和状态.因此,如果有4个控制器,则视图应始终具有4行,每行具有不同的控制器及其最新状态.
我已经能够创建一个查询来获取想要的内容,但是它依赖于FROM子句中的子查询,而该查询在视图中是不允许的.这是我到目前为止的内容：
SELECT *
FROM
(SELECT
ControllerID, TimeStamp, State
FROM Metrics
ORDER BY TimeStamp DESC)
AS t
GROUP BY ControllerID;
就像我说的那样,效果很好.但是我不能在视图中使用它.我尝试使用max()函数,但按此处：SQL: Any straightforward way to order results FIRST, THEN group by another column?如果我想要除GROUP BY和ORDER BY列之外的任何其他列,则max()不起作用.我已经确认了此限制,但此限制无效.
我还尝试更改指标表以按TimeStamp排序.那也不行；保留了错误的行.
编辑：这是我从中提取的Metrics表的SHOW CREATE TABLE：
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
解决方法:
如果需要每个控制器的最新行,则下面的视图是友好的：
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
);
您的查询无论如何都不正确,因为它使用的MySQL扩展不能保证正常工作. state的值不一定来自时间戳最大的行.它来自任意行.
编辑：
为了获得最佳性能,您需要在Metrics(ControllerId,Timestamp)上建立索引.
标签：sql,mysql
来源： https://codeday.me/bug/20191120/2046808.html