mysql - 查询获取MAX值和对应的日期

Question

桌子

+---------------------+-----------------------------+--------------+----------+
| timestamp           | hostname                    | LICENSECOUNT | MAX_USED |
+---------------------+-----------------------------+--------------+----------+
| 2021-02-03 17:00:03 | server1                     |          125 |      10  |
| 2021-02-03 17:00:06 | server2                     |          415 |      50  |
| 2021-02-26 18:00:07 | server1                     |          125 |      125 |
| 2021-02-26 18:00:09 | server2                     |          415 |      415 |
| 2021-03-05 17:00:02 | server1                     |          125 |      115 |
| 2021-03-05 17:00:04 | server2                     |          415 |      315 |

我的查询- 获取过去 30 天内按服务器分组的许可证计数的最大值

select 
    timestamp, hostname, LICENSECOUNT, max(currentused) as MAX_USED 
from 
    LICENSECHECKS 
where 
    TIMESTAMP BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() 
group by hostname;

我的结果- 时间戳是数据集的开始（30 天前）

+---------------------+-----------------------------+--------------+----------+
| timestamp           | hostname                    | LICENSECOUNT | MAX_USED |
+---------------------+-----------------------------+--------------+----------+
| 2021-02-03 17:00:03 | server1                     |          125 |      125 |
| 2021-02-03 17:00:06 | server2                     |          415 |      415 |

我想要的结果- 时间戳对应于日期

+---------------------+-----------------------------+--------------+----------+
| timestamp           | hostname                    | LICENSECOUNT | MAX_USED |
+---------------------+-----------------------------+--------------+----------+
| 2021-02-26 18:00:07 | server1                     |          125 |      125 |
| 2021-02-26 18:00:09 | server2                     |          415 |      415 |

标签： mysqlsql

Answer 1

在子查询选择语句中添加以下内容

 ROW_NUMBER() OVER (PARTITION BY hostname ORDER BY currentuserid DESC) as RN

然后在外部查询 where 语句中提取 RN = 1 的行