基于日期范围的Object类型Modifiable状态SQL查询需求

表结构与数据 #

1. 状态变更记录表（假设表名为object_status_changes） #

这张表存储对象的状态变更历史，Date字段为INT类型：

2. 待检查时间范围表（假设表名为date_ranges） #

这张表存储需要检查的时间区间：

业务需求 #

针对每个时间范围，判断是否存在某个Object同时满足：

• 该Object的Type 1在时间段内有被设置为Modifiable的记录
• 该Object的Type 2在时间段内有被设置为Modifiable的记录

最终结果需包含SystemModifiable（值为Yes或No）、start_date、end_date三个字段。

用户尝试的伪代码 #

Case语句伪代码 #

Case When ((Type=1 And StatusNew=Modifiable) And (Type=2 And StatusNew=Modifiable)) And -- For each date range-- Then 'Yes' Else 'No' End As SystemModifiable

逻辑伪代码 #

IF (Type=1 AND StatusNew=modifiable AND Type=2 AND StatusNew=modifiable) 
    SET SystemModifiable to 'Yes' 
ELSE 
    SET SystemModifiable to 'No'

最终SQL实现 #

这里我们使用EXISTS子查询来判断每个时间范围内是否存在符合条件的Object，逻辑清晰且性能友好：

SELECT
    dr.start_date,
    dr.end_date,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM object_status_changes osc1
            INNER JOIN object_status_changes osc2 
                ON osc1.Object = osc2.Object
            WHERE 
                -- 匹配Type1的Modifiable变更且在时间范围内
                osc1.Type = 1 
                AND osc1.StatusNew = 'Modifiable'
                AND osc1.Date BETWEEN dr.start_date AND dr.end_date
                -- 匹配同一Object的Type2的Modifiable变更且在时间范围内
                AND osc2.Type = 2 
                AND osc2.StatusNew = 'Modifiable'
                AND osc2.Date BETWEEN dr.start_date AND dr.end_date
        ) THEN 'Yes'
        ELSE 'No'
    END AS SystemModifiable
FROM date_ranges dr;

代码解释 #

• 外层查询遍历所有待检查的时间范围
• 内层EXISTS子查询通过自连接状态变更表，找到同一个Object下，Type1和Type2都在当前时间范围内被设置为Modifiable的记录
• 如果存在这样的记录，SystemModifiable设为Yes，否则为No

示例结果验证 #

根据你提供的测试数据，两个时间范围都没有符合条件的Object（第一个范围无任何变更记录，第二个范围截止到20011002，而所有变更记录都在20011003及之后），所以结果会和你给出的示例完全一致：

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