mysql - 按时间戳选择最近的不同对

Question

我有一个带有列的表：

• id（自增）
• created_at
• topic_id
• code_id
• 价值

该表有许多具有相同（topic_id，code_id）的行。

对于给定的主题 ID 和代码 ID 列表，我想选择具有唯一 (topic_id, code_id) 组合的最新行（= 最大 created_at）。

我试过了：

select distinct topic_id, code_id from topic_values where topic_id in (...) and code_id in (...) order by created_at desc;

这是错误的，因为：

• 它只选择 topic_id 和 code_id
• 全局按latest created_at排序（按latest created_at排序应在(topic_id,code_id)范围内。

然后我尝试了：

select * from topic_values where topic_id in (...) and code_id in (...) group by topic_id, code_id order by created_at desc;

这是错误的，因为：

• 全局按latest created_at排序（按latest created_at排序应在(topic_id,code_id)范围内。

我怎么能做到这一点？理想情况下，我希望查询利用我在表上的以下索引：

  KEY `created_at` (`created_at`,`topic_id`,`code_id),
  KEY `created_at_2` (`created_at`,`code_id`,`topic_id`),
  KEY `topic_id` (`topic_id`,`code_id`,`created_at`),
  KEY `topic_id_2` (`topic_id`,`created_at`,`code_id`),
  KEY `code_id` (`code_id`,`topic_id`,`created_at`),
  KEY `code_id_2` (`code_id`,`created_at`,`topic_id`),

标签： mysqlsql

Answer 1

使用窗口函数按日期降序查找每个组顺序的第一行

select *
from (
  select *, row_number() over(partition by topic_id, code_id order by created_at desc) rn
  from topic_values
  where topic_id in (1,2,3) and code_id in (4,5,6)
) r where rn = 1
order by id

或每个组的最大日期的子查询

select a.*
from topic_values a
join (
  select topic_id, code_id, max(created_at) max_date
  from topic_values
  where topic_id in (1,2,3) and code_id in (4,5,6)
  group by topic_id, code_id
) r on a.topic_id = r.topic_id and a.code_id = r.code_id and a.created_at = r.max_date
where
 a.topic_id in (1,2,3) and a.code_id in (4,5,6)
order by id

第二个选项将获得多行，如果它发生多行具有相同的 max created_at 相同的 topic_id 和 code_id。如果需要，它可以row_number()在第一个选项中替换为rank()

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d4d26dffc115c9610c6e21a55d149676