mysql - 如何在子查询中使用第一个查询的结果

我正在尝试针对在grafana仪表板中使用的MySQL数据库编写查询。当我从表中手动选择一个已知的时间戳时，我有一个有效的查询，但是当我事先不知道该时间戳时，我的数据是不正确的。

目标是获得仅使用1个TimeStmp即可获得4个不同名称中的UsedSpaceMB总量。即在TimeStmp之类的“％2018-07-27 16％”中。如果查询中包含多个TimeStmp，则UsedpaceMB的总量不正确。

表名称为存储
字段是TimeStmp，UsedSpaceMB和Name

Select TimeStmp, UsedSpaceMB, Name FROM Storage LIMIT 5;
+---------------------+-------------+------------------------+
| TimeStmp            | UsedSpaceMB | Name                   |
+---------------------+-------------+------------------------+
| 2018-07-27 16:02:07 |    61415607 | Array1                 |
| 2018-07-27 20:02:09 |    61896587 | Array1                 |
| 2018-07-28 00:02:17 |    61767053 | Array1                 |
| 2018-07-28 04:02:04 |    62175704 | Array1                 |
| 2018-07-28 08:02:10 |    62683011 | Array1                 |
+---------------------+-------------+------------------------+

SELECT DISTINCT NAME FROM Storage ORDER BY Name ASC;
+------------------------------+
| NAME                         |
+------------------------------+
| Array1                       |
| Array2                       |
| Array3                       |
| Array4                       |
+------------------------------+

该查询有效：

 SELECT
 TimeStmp as time,
'Used Capacity' as metric,
(SELECT SUM(UsedSpaceMB) as value)
FROM Storage
WHERE TimeStmp LIKE "%2018-07-27 16:%" AND Name IN ("Array1", "Array2", "Array3", "Array4")

（示例将To Time设置为2018-08-15 04:00的示例）

 SELECT
 TimeStmp as time,
'Used Capacity' as metric,
(SELECT SUM(UsedSpaceMB) as value)
FROM Storage
WHERE TimeStmp >= FROM_UNIXTIME($__unixEpochTo(),'%Y-%m-%d %H') AND Name IN ("Array1", "Array2", "Array3", "Array4")

我真正想做的是首先在原始查询中选择MAX（TimeStmp），然后在WHERE子句中使用它，以便我仅搜索1个特定的TimeStmp（即WHERE TimeStmp = MAX（TimeStmp）或HAVING TimeStmp = MAX（TimeStmp）。我不确定如何执行JOIN或使用HAVING，因为执行此查询时没有任何结果。

 SELECT
 TimeStmp as time,
'Used Capacity' as metric,
(SELECT SUM(UsedSpaceMB) as value)
FROM Storage
WHERE Name IN ("Array1", "Array2", "Array3", "Array4")
HAVING TimeStmp = MAX(TimeStmp)

预期结果：

SELECT (SELECT SUM(UsedSpaceMB)) FROM Storage WHERE TimeStmp LIKE "%2018-07-27 16:%" AND Name IN('Array1','Array2','Array3','Array4');
+---------------------------+
| (SELECT SUM(UsedSpaceMB)) |
+---------------------------+
|                 777379124 |
+---------------------------+

最佳答案

如果要按名称对UsedSpaceMB行的总和（假定每个名称有多个行，则为一个时间戳）：

SELECT
    a.`Name`,
    a.`TimeStamp` as `time`,
    'Used Capacity' as `metric`,
    SUM(a.`UsedSpaceMB`) as `value`
FROM `Storage` a
JOIN (SELECT MAX(`TimeStamp`) as `tm`,`Name`
        FROM `Storage`
        WHERE `Name` IN ("Array1", "Array2", "Array3", "Array4")
    ) b
    ON a.`TimeStamp` = b.`tm` AND a.`Name` = b.`Name`
GROUP BY a.`Name`
ORDER BY a.`Name`

另外，不确定何时可以使用(SELECT SUM(UsedSpaceMB) as value)时为什么使用SUM(UsedSpaceMB) as value。