mysql - 在一个sql查询中选择不同时间集中的结果

Question

如何在一次查询中选择 5 秒、5 分钟、5 小时、5 天、5 周、5 个月、5 年中添加的最后一条记录？

是否可以在一次查询中选择？

示例记录包括

name | added_timestamp
v | last_five_minutes
k | last_hour
l | last_hour

时间戳可以是伪实际时间

Answer 1

虽然您没有说明，但我怀疑您想要不同时间段的总计:

SELECT
  SUM(date_added > NOW() - INTERVAL 5 SECOND) as total_in_last_5_seconds,
  SUM(date_added > NOW() - INTERVAL 5 MINUTE) as total_in_last_5_minutes,
  SUM(date_added > NOW() - INTERVAL 5 HOUR) as total_in_last_5_hours,
  SUM(date_added > NOW() - INTERVAL 5 DAY) as total_in_last_5_days,
  SUM(date_added > NOW() - INTERVAL 5 WEEK) as total_in_last_5_weeks,
  SUM(date_added > NOW() - INTERVAL 5 MONTH) as total_in_last_5_months,
  SUM(date_added > NOW() - INTERVAL 5 YEAR) as total_in_last_5_years
from records;

已编辑:添加了评论提示的替代方案

如果您想要实际记录，这将对它们进行分类:

SELECT
  *,
  case
    when date_added > NOW() - INTERVAL 5 SECOND then 'last_5_seconds'
    when date_added > NOW() - INTERVAL 5 MINUTE then 'last_5_minutes'
    when date_added > NOW() - INTERVAL 5 HOUR then 'last_5_hours'
    when date_added > NOW() - INTERVAL 5 DAY then 'last_5_days'
    when date_added > NOW() - INTERVAL 5 WEEK then 'last_5_weeks'
    when date_added > NOW() - INTERVAL 5 MONTH then 'last_5_months'
    when date_added > NOW() - INTERVAL 5 YEAR then 'last_5_years'
    else 'ancient'
  end as time_category
from records;