SQL每日一题(20201119)两个时间段内的最早的一条记录视为“有效”，在这两个时间段内其它打卡数据显示“重复”，否则视为“无效”

本文链接：https://blog.csdn.net/weixin_44681259/article/details/124156804

SQL每日一题(20210507)
SQL每日一题(20210816)
SQL每日一题(20211102)
SQL每日一题(20220415)
SQL每日一题(20211228)
在这里插入图片描述

select userid,
       checkin,
       case
           when (ca = '上午' and rn = 1) or (ca = '下午' and rn = 1) then '有效'
           when ca = '其他' then '无效'
           ELSE '重复' end as '状态'
from (select *,
             rank() over (PARTITION by userid,date(CheckIn),case
                                                                when time(CheckIn) BETWEEN "08:00" AND "09:00" then '上午'
                                                                when time(checkin) BETWEEN "16:30" AND "18:00" then '下午'
                                                                else '其他' END ORDER BY time(checkin) ) as rn,
             case
                 when time(CheckIn) BETWEEN "08:00" AND "09:00" then '上午'
                 when time(checkin) BETWEEN "16:30" AND "18:00" then '下午'
                 else '其他' END ca from t1119) t ORDER BY userid asc,date(CheckIn)asc ,time(checkin) asc

在这里插入图片描述

SELECT t1.*,
       IF((DATE_FORMAT(t1.CheckIn, '%H:%i:%s') >= '08:00' AND DATE_FORMAT(t1.CheckIn, '%H:%i:%s') <= '09:00') OR
          (DATE_FORMAT(t1.CheckIn, '%H:%i:%s') >= '16:30' AND DATE_FORMAT(t1.CheckIn, '%H:%i:%s') <= '18:00'),
          IF(t_am_pm.UserId IS NOT NULL, '有效', '重复'), '无效') AS `状态`
FROM T1119 t1
         LEFT JOIN (
    SELECT t3.UserId, MIN(t3.CheckIn) AS CheckIn
    FROM T1119 t3
    WHERE (
                  (DATE_FORMAT(t3.CheckIn, '%H:%i:%s') >= '08:00' AND DATE_FORMAT(t3.CheckIn, '%H:%i:%s') <= '09:00')
                  OR (DATE_FORMAT(t3.CheckIn, '%H:%i:%s') >= '16:30' AND DATE_FORMAT(t3.CheckIn, '%H:%i:%s') <= '18:00')
              )
    GROUP BY t3.UserId, DATE_FORMAT(t3.CheckIn, '%Y-%m-%d%p')
) AS t_am_pm ON t1.UserId = t_am_pm.UserId AND t1.CheckIn = t_am_pm.CheckIn
ORDER BY t1.UserId ASC, t1.CheckIn ASC