SQL查询：如何获取指定日期范围内无效的ITEMID记录？

我的数据库中有一张表，部分物品对应多条记录，包含字段ITEMID、VALIDFROM、VALIDTO。需要查询在TODATE的23:59:59时刻处于无效状态的ITEMID。

示例数据 #

ITEMID    |VALIDFROM           |VALIDTO
---------------------------------------------------------------------------------
1         |01/01/2024 00:00:00 |02/01/2024 23:59:59
1         |04/01/2024 00:00:00 |10/01/2024 23:59:59
1         |15/01/2024 00:00:00 | null      (null表示无过期日期)
2         |30/12/2023 00:00:00 |02/01/2024 23:59:59
3         |31/12/2023 00:00:00 | null      (null表示无过期日期)
4         |30/12/2023 00:00:00 |03/01/2024 23:59:59
5         |03/01/2024 01:00:00 |03/01/2024 02:00:00
6         |02/01/2024 00:00:00 |03/01/2024 01:59:59
6         |03/01/2024 02:00:00 |03/01/2024 23:59:59

测试条件与期望结果 #

当指定TODATE = '2024-01-03 23:59:59'时，期望得到以下ITEMID：

ITEMID
---------------------------------------------------------------------------------
1
2
4
5
6

解决方案SQL #

思路：排除目标时刻有效的ITEMID #

先找出所有在2024-01-03 23:59:59处于有效状态的ITEMID，再从总列表中排除这些ID，剩下的就是无效的。

-- 替换your_table为你的实际表名
SELECT DISTINCT ITEMID
FROM your_table
WHERE ITEMID NOT IN (
    SELECT ITEMID
    FROM your_table
    WHERE VALIDFROM <= '2024-01-03 23:59:59'
      AND (VALIDTO IS NULL OR VALIDTO >= '2024-01-03 23:59:59')
);

思路二：直接判断无有效记录覆盖目标时刻 #

用NOT EXISTS判定某个ITEMID不存在任何一条记录覆盖目标时间：

-- 替换your_table为你的实际表名
SELECT DISTINCT t1.ITEMID
FROM your_table t1
WHERE NOT EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.ITEMID = t1.ITEMID
      AND t2.VALIDFROM <= '2024-01-03 23:59:59'
      AND (t2.VALIDTO IS NULL OR t2.VALIDTO >= '2024-01-03 23:59:59')
);

逻辑说明 #

• 有效状态判定：某条记录的VALIDFROM早于/等于目标时刻，且VALIDTO为NULL（永久有效）或晚于/等于目标时刻。
• 无效状态的ITEMID：该ID下没有任何一条记录满足上述有效条件。

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