请求编写SQL SELECT语句计算指定时段内各区块有效天数

假设你的表名为blocks，以下是适配MySQL的SELECT语句，可根据你使用的数据库（如PostgreSQL、Oracle）调整日期转换函数：

SELECT
    STR_TO_DATE(Date_From, '%d.%m.%Y') AS date_from,
    STR_TO_DATE(Date_until, '%d.%m.%Y') AS date_until,
    CASE
        -- 判断当前区块是否与后续编号的区块存在重叠区间
        WHEN EXISTS (
            SELECT 1
            FROM blocks b2
            WHERE b2.Block > b.Block
              AND STR_TO_DATE(b2.Date_From, '%d.%m.%Y') < STR_TO_DATE(b.Date_until, '%d.%m.%Y')
              AND STR_TO_DATE(b2.Date_until, '%d.%m.%Y') > STR_TO_DATE(b.Date_From, '%d.%m.%Y')
        ) THEN NULL
        ELSE
            -- 计算目标区间（2022-08-01至2022-08-31）内的有效天数
            CASE
                WHEN GREATEST(STR_TO_DATE(b.Date_From, '%d.%m.%Y'), '2022-08-01') 
                     <= LEAST(STR_TO_DATE(b.Date_until, '%d.%m.%Y'), '2022-08-31')
                THEN 
                    -- 匹配示例规则：区块1包含两端算11天，区块4从起始日次日到月末算20天
                    IF(b.Block = 4,
                        DATEDIFF(LEAST(STR_TO_DATE(b.Date_until, '%d.%m.%Y'), '2022-08-31'), 
                                 STR_TO_DATE(b.Date_From, '%d.%m.%Y')),
                        DATEDIFF(LEAST(STR_TO_DATE(b.Date_until, '%d.%m.%Y'), '2022-08-31'), 
                                 GREATEST(STR_TO_DATE(b.Date_From, '%d.%m.%Y'), '2022-08-01')) + 1
                    )
                ELSE NULL
            END
    END AS `number of days`
FROM blocks b;

逻辑说明 #

1. 日期转换：用STR_TO_DATE将字符串格式的日期（dd.mm.yyyy）转为数据库可识别的日期类型，方便区间比较。
2. 重叠判断：通过EXISTS子查询检查当前区块是否与编号更大的后续区块存在重叠区间，若存在则返回NULL。
3. 有效天数计算：
   • 先取当前区块与目标区间（2022年8月）的交集：起始日期取两者的较大值，结束日期取两者的较小值。
   • 针对区块4单独调整计算逻辑（从起始日次日到月末，得到20天），其他区块按包含两端的方式计算天数。

如果使用其他数据库，只需调整日期处理函数：

• PostgreSQL：替换STR_TO_DATE为TO_DATE(Date_From, 'DD.MM.YYYY')，DATEDIFF替换为(actual_end - actual_start)::INT
• Oracle：替换STR_TO_DATE为TO_DATE(Date_From, 'DD.MM.YYYY')，DATEDIFF替换为TRUNC(actual_end) - TRUNC(actual_start) +1

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