基于单一日期字段（无结束日期）的ID筛选技术问询

嘿，我来帮你搞定这个需求！你要做的就是把Runtime表的每条运行记录，和Category表中对应ID在运行日期当天或之前最新生效的分类关联起来对吧？因为Category表只有生效日期没有结束日期，所以每个ID的分类是按时间递进覆盖的，咱们直接上解决方案：

先准备测试数据（方便你验证） #

-- 创建分类表
CREATE TABLE Category (
    ID INT,
    Active BIT,
    Category VARCHAR(1),
    Effective DATE
);

-- 插入示例数据
INSERT INTO Category VALUES
(1, 0, 'A', '2009-01-29'),
(1, 0, 'B', '2014-05-13'),
(1, 1, 'B', '2017-09-21'),
(2, 0, 'B', '2010-03-04'),
(2, 1, 'A', '2016-02-19'),
(3, 0, 'A', '2015-10-15'),
(3, 1, 'B', '2017-08-12');

-- 创建运行时间表
CREATE TABLE Runtime (
    ID INT,
    RunDate DATE
);

-- 补全示例数据
INSERT INTO Runtime VALUES
(1, '2015-06-14'),
(1, '2015-09-14'),
(1, '2017-10-04');

方案一：用窗口函数（推荐，高效简洁） #

窗口函数是处理这类“取分组内最新记录”场景的最佳选择，代码清晰且性能不错：

WITH RankedCategories AS (
    SELECT 
        r.ID,
        r.RunDate,
        c.Category,
        c.Effective,
        -- 按ID和运行日期分组，对符合条件的记录按生效日期倒序排名
        ROW_NUMBER() OVER (
            PARTITION BY r.ID, r.RunDate 
            ORDER BY c.Effective DESC
        ) AS rn
    FROM Runtime r
    LEFT JOIN Category c 
        ON r.ID = c.ID 
        AND c.Effective <= r.RunDate -- 只匹配运行日期之前生效的记录
)
SELECT ID, RunDate, Category
FROM RankedCategories
WHERE rn = 1; -- 取每组排名第一的（也就是最新生效的那条）

逻辑说明： #

1. 先把Runtime和Category关联，只保留ID相同且生效日期不晚于运行日期的记录；
2. 用ROW_NUMBER()给每个(ID, RunDate)组的记录按生效日期从新到旧排名；
3. 最后筛选出排名为1的记录，就是对应运行日期时生效的分类。

方案二：子查询找最大生效日期（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如老版MySQL），可以用这种子查询的方式：

SELECT 
    r.ID,
    r.RunDate,
    c.Category
FROM Runtime r
LEFT JOIN (
    -- 先算出每个(ID, RunDate)对应的最新生效日期
    SELECT 
        r_inner.ID,
        r_inner.RunDate,
        MAX(c_inner.Effective) AS MaxEffective
    FROM Runtime r_inner
    LEFT JOIN Category c_inner 
        ON r_inner.ID = c_inner.ID 
        AND c_inner.Effective <= r_inner.RunDate
    GROUP BY r_inner.ID, r_inner.RunDate
) max_eff ON r.ID = max_eff.ID AND r.RunDate = max_eff.RunDate
LEFT JOIN Category c 
    ON r.ID = c.ID 
    AND c.Effective = max_eff.MaxEffective;

逻辑说明： #

1. 子查询先找到每个运行记录对应的最大生效日期；
2. 再用这个日期回联Category表，拿到对应的分类信息。

测试结果 #

运行上面的代码，你会得到这样的结果：

完全符合需求：前两条运行日期在2017年9月之前，匹配的是2014年生效的B分类；最后一条在2017年9月之后，匹配的是最新生效的B分类。

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