如何利用子查询返回的动态时间戳范围筛选另一张表的匹配记录

错误原因 #

你原有写法的问题在于WHERE条件中(SELECT t1 FROM R)和(SELECT t2 FROM R)属于标量子查询，当R表返回多行范围时，大部分数据库要么直接抛出「子查询返回多行」的错误，要么默认取第一行的t1、t2值进行判断，因此只能匹配第一个时间范围。

正确实现方案 #

方案1：EXISTS子查询（兼容性最好，全SQL标准支持）

这是最通用的实现方式，无需处理重复记录问题，性能稳定：

WITH R(t1,t2) AS (
   << 用于获取t1、t2行的自定义查询 >>
)
SELECT * 
FROM MyTable m
WHERE EXISTS (
    SELECT 1 FROM R 
    WHERE m.t BETWEEN R.t1 AND R.t2
);

逻辑说明：对MyTable的每一条记录，遍历R表的所有时间范围，只要存在任意一个范围满足t落在区间内，就保留该记录。

方案2：关联JOIN实现

如果你的场景中时间范围没有重叠，也可以用内连接实现，若存在重叠范围需要加DISTINCT去重：

WITH R(t1,t2) AS (
   << 用于获取t1、t2行的自定义查询 >>
)
SELECT DISTINCT m.*
FROM MyTable m
JOIN R ON m.t BETWEEN R.t1 AND R.t2;

如果确认所有范围无重叠，可去掉DISTINCT获得更高的查询性能。

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