This question already has answers here:
Get records with max value for each group of grouped SQL results
                                
                                    (18个回答)
                                
                        
                                12个月前关闭。
            
                    
我有一个称为度量的实体表,每个度量都有一个随时间变化的值。我将此变化值存储在另一个表中,该表称为metric_history。

指标:

id, name, category
1, File, Disk
2, CPU, Hardware
3, Memory, Hardware
4, MySQL, Database


metric_history:

id, mId, value, recordCreated
1, 1, lorem, 2019-02-06 15:19:33
2, 1, ipsum, 2019-02-06 15:19:43
3, 2, at, 2019-02-06 15:37:48
4, 1, dolor, 2019-02-06 15:41:31
5, 2, vero, 2019-02-06 15:42:02
6, 1, sit, 2019-02-06 15:47:15
7, 2, eos, 2019-02-06 15:49:11
8, 1, amet, 2019-02-06 15:50:59
9, 4, sed, 2019-02-06 15:54:09


我正在尝试建立一个查询,该查询将为我返回每个查询的最新时间戳,因此在示例中将是:

结果:

8, 1, amet, 2019-02-06 15:50:59
7, 2, eos, 2019-02-06 15:49:11
9, 4, sed, 2019-02-06 15:54:09


我似乎四处逛逛,试图找到一个优雅的查询,但现在怀疑我的餐桌设计。这是我的位置,似乎可以正常工作:

SELECT id, dmId, value, recordCreated
FROM metric_history
WHERE (dmId, recordCreated)
IN (SELECT DISTINCT dmId, MAX(recordCreated) FROM metric_history GROUP BY dmId)


我可以看到的另一种选择是在指标表中有一个额外的timestamp列,并在每次更新历史记录表时对其进行更新,但这似乎也不对。

我应该在这里写查询还是我的表设计错误?我倾向于前者。

最佳答案

使用MySQL-8.0,MariaDB-10.2,您可以使用窗口函数获取答案like this

SELECT id, mId, value, recordCreated
FROM (
  SELECT
    id, mId, value, recordCreated,
    ROW_NUMBER() OVER (PARTITION BY mId ORDER BY recordCreated DESC) AS `rank`
  FROM metric_history
  ORDER BY id
) AS tmp
WHERE tmp.`rank` = 1

10-05 20:32
查看更多