如何在SQL查询指定日期范围数据时额外返回前后各一条填充数据

只要你为DATETIME列建立了索引，下面的方案比你原本三次查询再合并的性能更高，全程走索引无全表扫描，也不需要应用层做额外的结果拼接：

SELECT * FROM MYTABLE 
WHERE [DATETIME] >= '2018/01/01 03:00' AND [DATETIME] < '2018/01/01 11:00'

UNION ALL

-- 取范围之前最近的1条
SELECT * FROM MYTABLE
WHERE [DATETIME] < '2018/01/01 03:00'
ORDER BY [DATETIME] DESC
LIMIT 1

UNION ALL

-- 取范围之后最近的1条
SELECT * FROM MYTABLE
WHERE [DATETIME] >= '2018/01/01 11:00'
ORDER BY [DATETIME] ASC
LIMIT 1

-- 可选：如果需要结果按时间升序排列，加上这行
ORDER BY [DATETIME] ASC

不同数据库的取前1条语法有差异，按需调整即可：

• SQL Server 把LIMIT 1替换为TOP 1，写在SELECT关键字之后
• Oracle 把LIMIT 1替换为FETCH FIRST 1 ROW ONLY，写在对应子句的ORDER BY之后

如果你用的是支持窗口函数的数据库版本（MySQL 8.0+/PostgreSQL/SQL Server 2012+/Oracle），需要后续扩展逻辑比如取前后各N条的话，可以用窗口函数写法，逻辑更统一：

WITH range_tag AS (
    SELECT *,
        CASE 
            WHEN [DATETIME] >= '2018/01/01 03:00' AND [DATETIME] < '2018/01/01 11:00' THEN 1
            WHEN [DATETIME] < '2018/01/01 03:00' THEN 2
            ELSE 3 
        END AS range_type
    FROM MYTABLE
),
ranked_data AS (
    SELECT *,
        ROW_NUMBER() OVER(
            PARTITION BY range_type 
            ORDER BY 
                CASE WHEN range_type=2 THEN [DATETIME] END DESC,
                CASE WHEN range_type=3 THEN [DATETIME] END ASC
        ) AS rn
    FROM range_tag
)
SELECT * FROM ranked_data
WHERE range_type=1 OR (range_type IN (2,3) AND rn=1)
ORDER BY [DATETIME] ASC

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