SQLite按指定时间区间存在性筛选唯一pk_position

嘿，我来帮你搞定这个SQLite的查询需求！咱们的目标是找出那些在两个指定时间区间内都至少有一条对应记录的唯一pk_position值，对吧？

核心思路 #

我们需要确保每个候选的pk_position同时满足两个条件：在第一个时间区间[day1:day2]存在记录，并且在第二个时间区间[day3:day4]也存在记录。这里有两种常用的实现方式，你可以根据自己的表数据量来选择。

方法一：分组+条件聚合（直观易读） #

这种方法适合中小规模的表，逻辑清晰，容易理解：

SELECT pk_position
FROM your_table_name  -- 记得替换成你的实际表名
WHERE datetime BETWEEN 'day1' AND 'day2'
   OR datetime BETWEEN 'day3' AND 'day4'
GROUP BY pk_position
HAVING 
  -- 检查该位置在第一个区间是否有至少一条记录
  COUNT(CASE WHEN datetime BETWEEN 'day1' AND 'day2' THEN 1 END) >= 1
  -- 同时检查在第二个区间是否有至少一条记录
  AND COUNT(CASE WHEN datetime BETWEEN 'day3' AND 'day4' THEN 1 END) >= 1;

代码解释：

1. WHERE子句先筛选出两个时间区间内的所有记录，减少后续分组的数据量
2. GROUP BY pk_position把同一个位置的所有记录归为一组
3. HAVING子句里的两个条件分别验证：该分组在第一个区间、第二个区间是否都存在有效记录（CASE语句在符合区间条件时返回1，否则返回NULL，COUNT会自动忽略NULL值，所以只要计数≥1就说明有记录）

方法二：EXISTS子查询（性能更优） #

如果你的表数据量很大，这种方法的查询效率会更高，因为它不需要分组统计：

SELECT DISTINCT pk_position
FROM your_table_name t1
WHERE 
  -- 验证该位置在第一个区间有记录
  EXISTS (
    SELECT 1 
    FROM your_table_name t2
    WHERE t2.pk_position = t1.pk_position
      AND t2.datetime BETWEEN 'day1' AND 'day2'
  )
  -- 同时验证该位置在第二个区间有记录
  AND EXISTS (
    SELECT 1 
    FROM your_table_name t3
    WHERE t3.pk_position = t1.pk_position
      AND t3.datetime BETWEEN 'day3' AND 'day4'
  );

代码解释：

• EXISTS子查询会快速检查是否存在符合条件的记录，一旦找到就停止查找，比全表分组统计更快
• DISTINCT确保最终结果里的pk_position是唯一的

关于示例结果为空的说明 #

你提到当int1=["20170201":"20170202"]、int2=["20170202":"20170203"]时查询结果为空，这说明你的数据中没有任何一个pk_position同时满足在第一个区间有记录、且在第二个区间有记录。举个例子：

• 可能所有位置要么只在20170201-20170202有记录，要么只在20170202-20170203有记录，没有重叠满足两个条件的位置
• 哪怕某个位置在20170202有记录，只要它在第一个区间（包含20170202）和第二个区间（也包含20170202）都有这条记录，那它应该会被查询出来——如果没出现，说明你的数据里不存在这样的位置哦。

使用提示 #

记得把代码里的your_table_name替换成你实际的表名，day1、day2、day3、day4替换成具体的YYYYMMDD格式字符串（比如'20170201'）。

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