如何按指定Datetime筛选SQL表？获取各位置指定日期前最新数据

嘿，这个需求我太熟了！刚好可以给你拆解清楚，结合你提到的GROUP BY思路，再讲讲SQL里datetime相关的内置功能怎么用～

先把你的示例场景明确下：

示例数据：

Location	Value	Datetime
A	5	2011-01-01
B	6	2011-01-01
A	7	2012-01-01
A	8	2012-06-01

指定日期：2012-05-01，期望返回B的6和A的7这两行。

你的核心思路是对的：先按location分组，拿到每个组里小于指定日期的最大datetime，再关联回原表拿到对应的value。下面给你两种常用实现方式，顺便讲datetime的内置功能用法：

方法一：子查询+关联（兼容绝大多数数据库） #

这种写法不需要依赖高级窗口函数，老版本数据库也能跑：

SELECT t1.*
FROM your_table t1
JOIN (
    -- 子查询：按location分组，取每个组内<=指定日期的最大datetime
    SELECT 
        location, 
        MAX(datetime) AS latest_dt  -- MAX()直接对datetime类型生效，自动按时间取最大值
    FROM your_table
    WHERE datetime <= '2012-05-01'  -- datetime直接和日期字符串比较，数据库会自动转换格式
    GROUP BY location
) t2 ON t1.location = t2.location AND t1.datetime = t2.latest_dt;

这里用到的datetime内置能力：

• MAX(datetime)：聚合函数对datetime类型完全支持，因为datetime本质是有序的时间戳，数据库会自动按时间先后判断大小，直接取每组的最新时间。
• 日期比较：直接用datetime <= 'YYYY-MM-DD'的格式即可，只要字符串符合数据库的日期规范（比如大部分数据库都支持YYYY-MM-DD），会自动隐式转换为datetime类型进行比较。

方法二：窗口函数（更简洁的现代写法） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL、SQL Server等），这种写法更直观，不需要额外关联：

SELECT location, value, datetime
FROM (
    SELECT 
        *,
        -- 按location分组，每组内按datetime倒序排，给行标序号
        ROW_NUMBER() OVER (PARTITION BY location ORDER BY datetime DESC) AS rn
    FROM your_table
    WHERE datetime <= '2012-05-01'
) sub_query
WHERE rn = 1;  -- 取每组的第一行（也就是最新的那行）

这里的datetime核心用法还是排序与比较：ORDER BY datetime DESC让每组内最新的时间排在最前面，配合ROW_NUMBER()就能轻松定位到目标行。

关于Datetime内置功能的细节补充 #

如果你的需求涉及更复杂的日期处理，比如忽略时间只按日期比较、动态计算指定日期，可以用这些内置函数：

• 截断时间，只保留日期：
  • MySQL：DATE(datetime) <= '2012-05-01'
  • PostgreSQL：DATE(datetime) <= '2012-05-01' 或 datetime <= '2012-05-01'::date
  • SQL Server：CAST(datetime AS DATE) <= '2012-05-01'
• 动态生成指定日期（比如取当前日期前7天）：
  • MySQL：DATE_SUB(CURDATE(), INTERVAL 7 DAY)
  • PostgreSQL：CURRENT_DATE - INTERVAL '7 days'
  • SQL Server：DATEADD(DAY, -7, GETDATE())

总之，datetime类型在SQL里的核心就是可比较、可排序、可被聚合函数（比如MAX）处理，你的GROUP BY思路完全没问题，只要把分组后的最大时间关联回原表就能拿到对应的数据啦～

内容的提问来源于stack exchange，提问作者Scarlet_Begonias