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

sczxawaw 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(334)

我正在尝试编写一个针对mysql数据库的查询，以便在grafana Jmeter 板中使用。当我从表中手动选择一个已知的时间戳时，我有一个工作查询，但是当我事先不知道时间戳时，我的数据是不正确的。
目标是在4个不同的名称中获得usedspacemb的总量，但只使用1个timestmp。i、 e其中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")

（例如，时间设置为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））。我不知道如何进行连接或使用having as当我执行此查询时，没有得到任何结果。

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 |
+---------------------------+

mysql grafana time-series

来源：https://stackoverflow.com/questions/51859563/how-to-use-result-of-first-query-in-subquery

2条答案

按热度按时间

xkftehaa1#

如果需要按名称列出的行的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 .

赞(0）回复(0）举报 2021-06-20

6kkfgxo02#

这就是你想要的吗？

SELECT SUM(UsedSpaceMB) AS total
FROM
(
    SELECT s1.UsedSpaceMB
    FROM Storage s1
    INNER JOIN
    (
        SELECT Name, MAX(TimeStmp) AS MaxTimeStmp
        FROM Storage
        GROUP BY Name
    ) s2
        ON s1.Name = s2.Name AND s1.TimeStmp = s2.MaxTimeStmp
) t;

赞(0）回复(0）举报 2021-06-20

我来回答

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

2条答案

相关问题

热门标签

最新问答