请求生成IN_OUT_RECORD表每日仅单条IN/OUT记录的SQL语句

对IN_OUT_RECORD表执行以下查询时：

select DISTINCT  * 
from IN_OUT_RECORD AD 
where (AD_USER_ID = 'ST1301220007') 
AND (AD.AD_DATE BETWEEN '2024-06-01 00:00:00' AND ('2024-06-12 23:59:00'));

返回结果中存在同一日期有多条IN或OUT记录的情况。现需修改SQL，实现每日仅保留1条最早的IN记录和1条最晚的OUT记录，若当天只有其中一种状态的记录，则保留该有效记录。

通过窗口函数ROW_NUMBER()按日期和状态分组筛选，可实现需求：

SELECT AD_ID, AD_USER_ID, AD_STATUS, AD_DATE
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY DATE(AD_DATE), AD_STATUS 
            ORDER BY CASE AD_STATUS WHEN 'IN' THEN AD_DATE ELSE AD_DATE DESC END
        ) AS rn
    FROM IN_OUT_RECORD
    WHERE AD_USER_ID = 'ST1301220007'
      AND AD_DATE BETWEEN '2024-06-01 00:00:00' AND '2024-06-12 23:59:00'
) t
WHERE rn = 1
ORDER BY DATE(AD_DATE), AD_STATUS;

语句说明 #

• PARTITION BY DATE(AD_DATE), AD_STATUS：按日期（提取AD_DATE的日期部分）和状态分组，确保同一天同状态的记录单独处理
• ORDER BY CASE AD_STATUS WHEN 'IN' THEN AD_DATE ELSE AD_DATE DESC END：对IN状态按时间升序取最早的一条，对OUT状态按时间降序取最晚的一条
• 外层筛选rn=1，保留每组的目标记录，最后按日期和状态排序

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