获取指定日期范围内MyTable每日首末条记录的SQL查询问题

问题根源 #

你原来的SQL错误在于用min(value)和max(value)取的是每日数值的最小、最大值，而非当日最早时间点、最晚时间点对应的记录值，这和需求不符。

方案1：窗口函数法（适配SQL Server、MySQL 8.0+、PostgreSQL等主流数据库） #

WITH DailyRecords AS (
    SELECT
        CAST(time AS DATE) AS Date,
        value,
        -- 按日期分组，给当日最早记录标记序号1
        ROW_NUMBER() OVER (PARTITION BY CAST(time AS DATE) ORDER BY time ASC) AS rn_asc,
        -- 按日期分组，给当日最晚记录标记序号1
        ROW_NUMBER() OVER (PARTITION BY CAST(time AS DATE) ORDER BY time DESC) AS rn_desc
    FROM MyTable
    -- 用>=和<避免漏掉2023-10-20当天的非0点数据
    WHERE time >= '2023-10-13' AND time < '2023-10-21'
)
SELECT
    Date,
    -- 提取当日第一条记录的value
    MAX(CASE WHEN rn_asc = 1 THEN value END) AS startVal,
    -- 提取当日最后一条记录的value
    MAX(CASE WHEN rn_desc = 1 THEN value END) AS endVal
FROM DailyRecords
GROUP BY Date
ORDER BY Date;

方案2：关联子查询法（兼容多数低版本数据库） #

SELECT
    CAST(t.time AS DATE) AS Date,
    -- 子查询取当日最早时间对应的value
    (SELECT TOP 1 value FROM MyTable WHERE CAST(time AS DATE) = CAST(t.time AS DATE) ORDER BY time ASC) AS startVal,
    -- 子查询取当日最晚时间对应的value
    (SELECT TOP 1 value FROM MyTable WHERE CAST(time AS DATE) = CAST(t.time AS DATE) ORDER BY time DESC) AS endVal
FROM MyTable t
WHERE t.time >= '2023-10-13' AND t.time < '2023-10-21'
GROUP BY CAST(t.time AS DATE)
ORDER BY Date;

注：如果是MySQL或PostgreSQL，需要把TOP 1替换为LIMIT 1。

额外说明 #

• 日期筛选不建议用BETWEEN：BETWEEN '2023-10-13' AND '2023-10-20'会漏掉2023-10-20当天除0点外的所有数据（当time包含时分秒时）。
• 若用SQL Server，CAST(time AS DATE)可替换为FORMAT(time, 'yyyy-MM-dd')，但CAST的性能更优。

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