SQL查询问题：日期筛选需含前置记录及无前置/空结果的处理

核心思路 #

拆分查询逻辑为两部分：

• 第一部分：获取用户指定日期范围内的所有订单记录
• 第二部分：获取指定起始日期之前的最近一条订单记录（如果存在）
  通过UNION ALL合并结果，同时处理无有效记录时返回默认值的场景，避免原查询中BETWEEN因子查询返回NULL导致的失效问题。

通用SQL查询语句 #

DECLARE @StartDate CHAR(8) = '20230101';
DECLARE @EndDate CHAR(8) = '20231231';
DECLARE @DefaultDate CHAR(8) = '19000101'; -- 根据业务需求调整默认值

WITH CombinedResults AS (
    -- 1. 获取日期范围内的订单记录
    SELECT OrderDate 
    FROM Orders
    WHERE OrderDate BETWEEN @StartDate AND @EndDate
    
    UNION ALL
    
    -- 2. 获取起始日期前的最近一条记录（不存在时返回NULL）
    SELECT MAX(OrderDate) 
    FROM Orders
    WHERE OrderDate < @StartDate
)
-- 返回有效结果（排除NULL）
SELECT OrderDate 
FROM CombinedResults
WHERE OrderDate IS NOT NULL

UNION ALL

-- 当无任何有效记录时返回默认值
SELECT @DefaultDate AS OrderDate
WHERE NOT EXISTS (
    SELECT 1 
    FROM CombinedResults 
    WHERE OrderDate IS NOT NULL
)

ORDER BY OrderDate;

场景验证 #

1. 存在前置记录的正常场景
   数据库订单日期：('20220701', '20221231', '20230101', '20230701', '20240101')
   查询结果：('20221231', '20230101', '20230701') —— 符合预期

2. 无前置记录的场景
   数据库订单日期：('20230101', '20230701', '20240101')
   查询结果：('20230101', '20230701') —— 正常返回范围内记录

3. 无任何匹配记录的场景
   数据库订单日期：('20221231', '20240101')，筛选2023年
   查询结果：('20221231') —— 返回前置记录
   若数据库无任何2023年前的记录，查询结果：('19000101') —— 返回默认值

关键优化点 #

• 用UNION ALL替代BETWEEN关联子查询，避免子查询返回NULL导致的筛选失效
• 通过IS NOT NULL排除前置记录查询返回的NULL值
• 用NOT EXISTS判断是否存在有效结果，按需返回默认值
• 参数化日期变量，提升查询的通用性和可维护性

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