关于从参考表取最新日期及NOT EXISTS子查询的技术咨询

Hey there! Let's tackle your SQL questions one by one, nice and clear.

获取参考表的最新日期，常见的方法分两种场景：

场景1：获取整张表的全局最新日期 #

最直接的方式是用MAX()聚合函数，简单高效：

SELECT MAX(the_date) AS latest_date
FROM your_reference_table;

场景2：按分组获取每个组的最新日期（比如按商品分组） #

如果需要每个分类下的最新日期，搭配GROUP BY就能实现：

SELECT good, MAX(the_date) AS latest_date_per_good
FROM price
GROUP BY good;

另外，也可以用窗口函数ROW_NUMBER()，适合需要同时获取最新日期对应其他字段的场景：

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY good ORDER BY the_date DESC) AS rn
    FROM price
) t
WHERE rn = 1;

首先，先拆解原SQL的逻辑：

SELECT i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price 
FROM inventory i 
LEFT JOIN price p ON p.good = i.good AND p.the_date <= i.the_date 
WHERE NOT EXISTS ( 
    SELECT 1 FROM price p1 
    WHERE p1.good = p.good AND p1.the_date <= i.the_date AND p1.the_date > p.the_date 
);

WHERE NOT EXISTS子查询的作用 #

这条SQL的核心是为每条库存记录（inventory），找到该记录日期之前的最新有效价格记录（price）。

具体来说：

1. 第一步LEFT JOIN先把所有和当前库存商品相同、且价格日期早于/等于库存日期的价格记录都关联过来
2. 然后WHERE NOT EXISTS子查询做过滤：对于每一条关联到的price记录，检查是否存在同商品、价格日期同样早于/等于库存日期，但比当前p.the_date更新的记录。如果不存在，说明当前p就是这条库存记录对应的最新有效价格——因为没有更晚的符合条件的价格了。

简单说，这个子查询就是帮我们“筛掉那些不是最新的价格记录”，只留下每个库存时间点对应的最新价格。

对比关联maxdate方案的优劣 #

先给出两种典型的“关联maxdate”方案写法（注意：这里要区分全局maxdate和按库存日期过滤的maxdate，很多人容易混淆）：

方案1：关联全局maxdate（每个商品的全局最新价格）

SELECT i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price
FROM inventory i
LEFT JOIN (
    SELECT good, MAX(the_date) AS max_date
    FROM price
    GROUP BY good
) p_max ON p_max.good = i.good
LEFT JOIN price p ON p.good = p_max.good AND p.the_date = p_max.max_date 
    AND p.the_date <= i.the_date; -- 加过滤是为了贴近原逻辑，但本质还是全局最新价格

方案2：关联按库存日期过滤的maxdate（更贴近原SQL逻辑）

SELECT i.the_date, p.the_date AS pricing_date, i.good, i.quantity, p.price
FROM inventory i
LEFT JOIN (
    SELECT i_inner.good, i_inner.the_date, MAX(p_inner.the_date) AS max_price_date
    FROM inventory i_inner
    LEFT JOIN price p_inner ON p_inner.good = i_inner.good AND p_inner.the_date <= i_inner.the_date
    GROUP BY i_inner.good, i_inner.the_date
) p_max ON p_max.good = i.good AND p_max.the_date = i.the_date
LEFT JOIN price p ON p.good = p_max.good AND p.the_date = p_max.max_price_date;

现在对比原NOT EXISTS方案和这两种maxdate方案的优劣：

NOT EXISTS方案的优势：

• 逻辑精准贴合需求：直接针对每条候选价格记录判断是否是“最新有效”，完美匹配“每个库存日期之前的最新价格”需求，不会像全局maxdate方案那样出现逻辑偏差
• 性能更优（在合适索引下）：如果price表有(good, the_date)的复合索引，NOT EXISTS的子查询可以快速定位是否存在符合条件的记录，避免分组聚合的排序开销
• 写法灵活：不需要提前聚合，直接在过滤环节处理，适合复杂的过滤条件

NOT EXISTS方案的劣势：

• 对新手不够友好：嵌套子查询的逻辑需要绕一下，不如分组聚合直观
• 执行计划稳定性：在某些数据库优化器中，NOT EXISTS的执行计划可能不如分组聚合稳定，需要结合实际数据量和索引情况调整

关联maxdate方案的优势：

• 结构清晰：分组聚合的写法更直观，容易理解“先找最新日期，再关联详情”的逻辑
• 复用性强：如果后续需要多次使用“每个商品/每个库存日期的最新价格日期”，提前聚合的结果可以复用

关联maxdate方案的劣势：

• 全局maxdate方案逻辑有缺陷：如果某个库存的日期早于该商品的全局最新价格日期，这个方案会关联不到价格，而原NOT EXISTS方案会找到该库存日期之前的最新价格，这是核心差异
• 性能开销大：分组聚合（尤其是按inventory的good和the_date分组）在数据量大的时候，排序和分组的开销会比NOT EXISTS的逐行判断高
• 写法繁琐：如果要贴合原SQL的“每个库存日期之前的最新价格”需求，需要先关联inventory和price再聚合，写法比NOT EXISTS更长

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