从一个带日期字段的业务表里取出倒数第三个有数据的日期，并把这一天的全部数据行捞出来，是一个典型的小练习，但实际写起来却比想象中容易出错。比如订单表 orders 里同一天可能产生几百条记录，如果直接对 created_at 倒序取前三行，拿到的只是最近三条订单，并不是倒数第三日的数据。本文先明确需求和常见误区，然后给出几种正确写法，并讨论哪些写法能在大表上保持较好的查询性能。
需求拆解与常见误区
所谓“倒数第三日”，通常指的是按日期去重之后，从最近一天开始往前数的第三个日期。例如订单表在 2024-04-01、2024-04-03、2024-04-05、2024-04-06 这四天有数据，那么倒数第三日就是 2024-04-03，因为最近一天是 6 日，往前数第二个是 5 日，第三个是 3 日。注意这里跳过了没有数据的 4 月 2 日和 4 月 4 日，因为我们要找的是表中存在过的日期，而不是自然日。
最常见的错误是直接把 created_at 倒序排列，然后 LIMIT 3 取三条。这种写法在每天只有一条数据时看似正常，但只要某一天有多条记录，结果就会完全偏离。比如 4 月 6 日有三条订单，那么 LIMIT 3 取出来的全部是 4 月 6 日的数据，根本没有定位到目标日期。另一个误区是拿最近的日期往前减三天，但如果中间有日期没有数据，也会选错。
所以关键动作有两个：先把时间字段转换成日期，再按日期去重，最后才是排序和取第三条。下面用一个最小表结构来做演示，字段包括订单 ID、用户 ID、金额和创建时间。
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME NOT NULL
);
INSERT INTO orders (order_id, user_id, amount, created_at) VALUES
(1, 101, 99.00, '2024-04-03 10:12:00'),
(2, 102, 55.00, '2024-04-03 18:30:00'),
(3, 103, 78.50, '2024-04-05 09:00:00'),
(4, 104, 120.00, '2024-04-05 14:20:00'),
(5, 105, 66.00, '2024-04-06 08:15:00'),
(6, 106, 88.00, '2024-04-06 09:45:00'),
(7, 107, 45.00, '2024-04-06 20:10:00');
从这份数据可以看到，4 月 3 日有两条记录，4 月 5 日有两条记录，4 月 6 日有三条记录。如果只想查出倒数第三个日期对应的全部数据，结果应当是 4 月 3 日的两条订单。
子查询方案：DISTINCT 与 OFFSET 定位目标日期
先从 orders 表里去重出所有出现过的日期，并倒序排列。这里可以用 DATE(created_at) 把 DATETIME 转成 DATE，再配合 DISTINCT 去重。因为要取倒数第三个日期，所以从排序结果里跳过前两行，取第三行，也就是 LIMIT 1 OFFSET 2。
SELECT DISTINCT DATE(created_at) AS order_date FROM orders WHERE created_at IS NOT NULL ORDER BY order_date DESC LIMIT 1 OFFSET 2;
这条语句返回 2024-04-03，正是我们需要的目标日期。接下来只需要把原表里创建时间落在这一天的记录全部取出来。最直接的写法是把上面的查询作为子查询放进 WHERE 条件里。
SELECT *
FROM orders
WHERE DATE(created_at) = (
SELECT DISTINCT DATE(created_at) AS order_date
FROM orders
WHERE created_at IS NOT NULL
ORDER BY order_date DESC
LIMIT 1 OFFSET 2
);
这样写逻辑清晰，适合数据量不大的练习场景。不过有一个明显缺点：WHERE 条件里对 created_at 使用了 DATE 函数，MySQL 无法直接使用 created_at 上的普通索引，可能会导致全表扫描。等数据量上来以后，这种写法就需要进一步优化。
另外还要注意，ORDER BY 后面的 order_date 是 SELECT 里的别名，MySQL 允许在 ORDER BY 中使用别名，但在子查询内部使用 LIMIT 和 OFFSET 时，必须保证排序方向正确。DESC 表示日期从近到远，OFFSET 2 正好跳过最近的两个日期。
窗口函数方案：DENSE_RANK 更直观
如果数据库是 MySQL 8 及以上版本，可以用窗口函数来完成同样的需求。DENSE_RANK() 会按照指定排序给每一行一个排名，关键特点是对相同的日期会给同一个排名，并且排名不会跳过数字。比如 4 月 6 日的多条记录都是第 1 名，4 月 5 日的多条记录都是第 2 名，4 月 3 日的记录就是第 3 名。这样我们只需要筛选排名等于 3 的行，就能拿到目标日期的全部数据。
WITH ranked_orders AS (
SELECT
o.*,
DENSE_RANK() OVER (ORDER BY DATE(created_at) DESC) AS rn
FROM orders o
WHERE created_at IS NOT NULL
)
SELECT order_id, user_id, amount, created_at
FROM ranked_orders
WHERE rn = 3;
这里没有使用 ROW_NUMBER()，是因为 ROW_NUMBER 会给每一行一个递增的唯一序号，同一天的多个订单会分别占据 1、2、3 等名次，这样倒数第三个日期反而可能对不上。DENSE_RANK 则完全符合按日期分组排名的需求。使用 CTE 可以让查询结构更清楚，也方便后续加条件或做统计。
窗口函数的好处是思路自然：先给每行打上日期排名，再按排名过滤。但它并不是大表上的最优方案，因为 DATE(created_at) 同样会让排序和分组操作难以利用索引。对于练习或中等数据量完全够用，生产环境可以结合下面的索引优化思路。
优化查询性能：生成列与范围条件
当订单表有几百万甚至几千万行时，频繁调用 DATE(created_at) 会造成不小的 CPU 开销，并且无法使用 created_at 索引。一个比较实用的办法是增加一个持久化的日期生成列，专门存放 DATE(created_at) 的结果，再对这个列建索引。生成列的值会随着原字段自动维护，查询时直接走索引即可。
ALTER TABLE orders
ADD COLUMN order_date DATE
GENERATED ALWAYS AS (DATE(created_at)) STORED;
CREATE INDEX idx_orders_order_date ON orders(order_date);
有了 order_date 列以后，定位目标日期的查询就可以改成 GROUP BY 配合索引排序，避免重复调用 DATE 函数。
SELECT o.*
FROM orders o
JOIN (
SELECT order_date
FROM orders
WHERE order_date IS NOT NULL
GROUP BY order_date
ORDER BY order_date DESC
LIMIT 1 OFFSET 2
) t ON o.order_date = t.order_date;
这个写法里，子查询只扫描 order_date 索引，取到目标日期后再回表关联。如果表中日期基数很大，索引扫描的效率会明显高于全表扫描。另一种更直接的优化是当已经拿到目标日期后，不要对 created_at 使用 DATE 函数，而是用半开区间条件，例如 WHERE created_at >= '2024-04-03' AND created_at < '2024-04-04'。这样可以完整命中 created_at 上的普通索引，尤其适合没有生成列的情况。
SELECT * FROM orders WHERE created_at >= '2024-04-03 00:00:00' AND created_at < '2024-04-04 00:00:00';
这里把目标日期作为变量传入即可。半开区间是处理日期范围的标准做法，下界包含，上界不包含，可以避免漏掉当天最后一秒的数据，也不会把次日零点整误算进来。如果时间字段是 TIMESTAMP 类型，还需要注意时区设置，DATE 的截取结果会随会话时区变化；而 DATETIME 不受时区影响，做日期维度统计时通常更省心。
边界情况与口径确认
在真实业务里，还要确认“倒数第三日”到底采用哪种口径。第一种是“表中存在过的日期”，也就是前面所有 SQL 默认的做法；第二种是“自然日里的倒数第三日”，也就是直接取当前日期往前推两天，无论那天有没有数据。两者的结果往往不同。如果业务要求的是自然日，SQL 会简单很多，只需要计算日期区间。
SELECT * FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL 2 DAY AND created_at < CURRENT_DATE - INTERVAL 1 DAY;
这条语句取的是前天的全天数据。比如今天是 2024-04-06，那么 CURRENT_DATE - INTERVAL 2 DAY 是 4 月 4 日，CURRENT_DATE - INTERVAL 1 DAY 是 4 月 5 日，查询条件就落在 4 月 4 日零点到 5 日零点之间。这里同样采用半开区间，能正确覆盖 4 月 4 日的全部记录。
另外还需要考虑数据不足三个日期的情况。如果表里只有两个不同的日期，子查询方案中的 LIMIT 1 OFFSET 2 会返回空结果，外层查询自然也为空。如果业务上希望在这种情况下退而求其次返回最旧的那一天，可以调整 OFFSET，或者使用窗口函数取最大排名小于等于 3 的日期。日期字段为 NULL 的情况也要提前过滤，否则排序和分组可能产生意外结果。
时间字段包含时分秒时，直接比较日期范围比截取日期更可靠。比如 created_at 存储了毫秒或微秒，使用 DATE(created_at) 会丢失精度但比较方式不受影响。关键是确定业务口径，再把对应的查询模板固化下来，后续维护会轻松很多。