区间重叠条件查询：验证指定区间是否与表中区间重叠

当然可以实现这个需求！我们需要先把存储在IntrvalStartPoints和IntervalEndPoints字段里的逗号分隔区间集合拆分成单独的区间行，再用区间重叠的核心判断逻辑来检查目标区间是否和任一现有区间重叠。

核心逻辑：区间重叠的判断条件 #

两个区间[目标起始, 目标终止]和[现有起始, 现有终止]重叠的充分必要条件是：

目标起始 < 现有终止 AND 现有起始 < 目标终止

这个条件能覆盖所有重叠场景（包含、交叉、部分覆盖等）。

分数据库实现示例 #

下面针对主流SQL数据库给出具体查询语句，假设你的表名为interval_table，目标参数为：ID=1000、目标起始点95005812、目标终止点95005815。

1. MySQL 8.0.19+ 或 MariaDB 10.5+ #

利用STRING_SPLIT配合ROW_NUMBER()来确保起始点和终止点一一对应：

SELECT EXISTS (
    SELECT 1
    FROM (
        -- 拆分起始点并生成行号
        SELECT 
            ID,
            CAST(value AS UNSIGNED) AS start_point,
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS rn
        FROM interval_table
        CROSS JOIN STRING_SPLIT(IntrvalStartPoints, ',')
        WHERE ID = 1000
    ) AS starts
    JOIN (
        -- 拆分终止点并生成行号
        SELECT 
            ID,
            CAST(value AS UNSIGNED) AS end_point,
            ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS rn
        FROM interval_table
        CROSS JOIN STRING_SPLIT(IntervalEndPoints, ',')
        WHERE ID = 1000
    ) AS ends ON starts.ID = ends.ID AND starts.rn = ends.rn
    -- 应用重叠判断条件
    WHERE 95005812 < ends.end_point AND starts.start_point < 95005815
) AS is_overlapping;

注：如果你的MySQL版本不支持STRING_SPLIT，可以用JSON_TABLE来替代拆分逻辑，把逗号分隔字符串转成JSON数组后拆分。

2. PostgreSQL #

使用string_to_array和unnest ... WITH ORDINALITY来拆分并保留区间的顺序对应：

SELECT EXISTS (
    SELECT 1
    FROM interval_table
    CROSS JOIN UNNEST(string_to_array(IntrvalStartPoints, ',')) WITH ORDINALITY AS s(start_point, rn)
    CROSS JOIN UNNEST(string_to_array(IntervalEndPoints, ',')) WITH ORDINALITY AS e(end_point, rn)
    WHERE interval_table.ID = 1000
      AND s.rn = e.rn
      -- 应用重叠判断条件
      AND 95005812::BIGINT < e.end_point::BIGINT
      AND s.start_point::BIGINT < 95005815::BIGINT
) AS is_overlapping;

3. SQL Server 2022+ #

利用STRING_SPLIT的ordinal参数直接关联对应位置的起始和终止点：

SELECT EXISTS (
    SELECT 1
    FROM (
        SELECT 
            ID,
            CAST(value AS BIGINT) AS start_point,
            ordinal
        FROM interval_table
        CROSS APPLY STRING_SPLIT(IntrvalStartPoints, ',', 1)
        WHERE ID = 1000
    ) AS starts
    JOIN (
        SELECT 
            ID,
            CAST(value AS BIGINT) AS end_point,
            ordinal
        FROM interval_table
        CROSS APPLY STRING_SPLIT(IntervalEndPoints, ',', 1)
        WHERE ID = 1000
    ) AS ends ON starts.ID = ends.ID AND starts.ordinal = ends.ordinal
    WHERE 95005812 < ends.end_point AND starts.start_point < 95005815
) AS is_overlapping;

注：SQL Server 2022之前的版本可以用XML拆分法来实现字符串拆分并保留顺序，核心逻辑一致。

结果说明 #

执行上述查询后，会返回1（存在重叠）或0（无重叠）。针对你给出的示例，目标区间<95005812,95005815>完全包含在原记录的<95005812,95005924>区间内，所以查询会返回1，表示存在重叠。

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