SQL中如何查询给定日期列表落在两日期列之间的所有行

核心逻辑 #

原查询匹配单个输入日期落在date1和date2区间内的行，改造后只要任意一个输入列表中的日期落在区间内，就返回该行，以下是不同场景的实现方式：

方案1：通用EXISTS写法（兼容所有主流数据库） #

先将传入的日期列表转为仅含input_date字段的临时表/表变量，再通过EXISTS子查询匹配：

SELECT * 
FROM tableA a
WHERE EXISTS (
    SELECT 1 
    FROM 输入日期临时表 d
    WHERE d.input_date BETWEEN a.date1 AND a.date2
)

不同数据库的参数适配示例 #

• PostgreSQL/MySQL 8.0+：支持直接传入数组参数，用UNNEST函数拆分数组为临时表：

-- 示例：日期列表以数组参数传入
SELECT * 
FROM tableA a
WHERE EXISTS (
    SELECT 1 
    FROM UNNEST(ARRAY['2024-01-01'::date, '2024-01-03'::date, '2024-01-07'::date]) d(input_date)
    WHERE d.input_date BETWEEN a.date1 AND a.date2
)

• SQL Server：可传入逗号分隔的日期字符串，用STRING_SPLIT拆分后转日期类型：

-- 示例：@IN_DateList 为逗号拼接的日期字符串参数，如 '2024-01-01,2024-01-03,2024-01-07'
SELECT * 
FROM tableA a
WHERE EXISTS (
    SELECT 1 
    FROM STRING_SPLIT(@IN_DateList, ',') d
    WHERE CAST(d.value AS DATE) BETWEEN a.date1 AND a.date2
)

• Oracle：可传入逗号分隔的日期字符串，用正则+层级查询拆分转日期：

-- 示例：:IN_DateList 为逗号拼接的日期字符串参数
SELECT * 
FROM tableA a
WHERE EXISTS (
    SELECT 1 
    FROM (
        SELECT TO_DATE(REGEXP_SUBSTR(:IN_DateList, '[^,]+', 1, LEVEL), 'yyyy-mm-dd') input_date
        FROM DUAL
        CONNECT BY REGEXP_SUBSTR(:IN_DateList, '[^,]+', 1, LEVEL) IS NOT NULL
    ) d
    WHERE d.input_date BETWEEN a.date1 AND a.date2
)

方案2：JOIN关联写法（需返回匹配日期时使用） #

如果需要同时返回该行匹配到的输入日期，可使用内连接实现：

SELECT DISTINCT a.*, d.input_date matched_input_date
FROM tableA a
INNER JOIN 输入日期临时表 d ON d.input_date BETWEEN a.date1 AND a.date2

注意：如果单条tableA记录匹配多个输入日期，会返回多条结果，加DISTINCT可对tableA的行去重。

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