如何编写SQL查询指定日期下contracts表中合同的激活状态

核心思路 #

只需找到每个合同在指定查询日期前的最后一条生效记录，根据该记录的类型即可判断合同状态：

• 最后一条记录类型为1/3 → 激活状态
• 最后一条记录类型为2 → 未激活状态

支持窗口函数的SQL实现（兼容MySQL 8+、PostgreSQL、SQL Server等主流数据库） #

-- 定义查询日期，可自行修改
SET @query_date = '2021-02-20';

WITH contract_events AS (
    -- 统一绑定每条记录对应的合同ID
    SELECT 
        CASE WHEN pid IS NULL THEN id ELSE pid END AS contract_id,
        id_type,
        start_date,
        -- 按日期倒序排序，取每个合同排在第一位的最新记录
        ROW_NUMBER() OVER (PARTITION BY CASE WHEN pid IS NULL THEN id ELSE pid END ORDER BY start_date DESC) AS rn
    FROM contracts
    WHERE start_date <= @query_date
)
SELECT 
    contract_id,
    CASE WHEN id_type IN (1,3) THEN '激活' ELSE '未激活' END AS contract_status
FROM contract_events
WHERE rn = 1;

结果验证 #

以查询日期2021-02-20为例，返回结果如下：

完全符合预期。

低版本MySQL（不支持窗口函数）实现方案 #

SET @query_date = '2021-02-20';

SELECT 
    t.contract_id,
    CASE WHEN c.id_type IN (1,3) THEN '激活' ELSE '未激活' END AS contract_status
FROM (
    SELECT 
        CASE WHEN pid IS NULL THEN id ELSE pid END AS contract_id,
        MAX(start_date) AS max_effect_date
    FROM contracts
    WHERE start_date <= @query_date
    GROUP BY contract_id
) t
INNER JOIN contracts c 
ON (c.id = t.contract_id AND c.pid IS NULL AND c.start_date = t.max_effect_date) 
OR (c.pid = t.contract_id AND c.start_date = t.max_effect_date);

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