-- 建表语句 CREATE TABLE T202303( USERID INT, CHECKIN DATE ); INSERT INTO T202303 (USERID, CHECKIN) VALUES (2, TO_DATE ('2018-12-01 18:02:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303 (USERID, CHECKIN) VALUES (1, TO_DATE ('2018-12-01 08:25:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303 (USERID, CHECKIN) VALUES (1, TO_DATE ('2018-12-01 08:26:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303 (USERID, CHECKIN) VALUES (1, TO_DATE ('2018-12-01 17:02:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303 (USERID, CHECKIN) VALUES (1, TO_DATE ('2018-12-02 08:27:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303 (USERID, CHECKIN) VALUES (2, TO_DATE ('2018-12-01 08:26:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303 (USERID, CHECKIN) VALUES (2, TO_DATE ('2018-12-01 17:03:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303 (USERID, CHECKIN) VALUES (2, TO_DATE ('2018-12-01 17:29:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303 (USERID, CHECKIN) VALUES (2, TO_DATE ('2018-12-01 18:01:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303 (USERID, CHECKIN) VALUES (1, TO_DATE ('2018-12-01 16:20:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303 (USERID, CHECKIN) VALUES (2, TO_DATE ('2018-12-01 16:02:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303 (USERID, CHECKIN) VALUES (2, TO_DATE ('2018-12-01 16:32:00', 'YYYY-MM-DD HH24:MI:SS')); INSERT INTO T202303(USERID, CHECKIN) VALUES (1,TO_DATE('2018-12-01 09:00:00','YYYY-MM-DD HH24:MI:SS')); COMMIT; 需求： 每天上午8:00-9:00，下午16:30-18:00这两个时间段内的最早的一条记录视为“有效”，在这两个时间段内其它打卡数据显示“重复”，否则视为“无效” 预计实现效果如下：

可以使用以下 SQL 语句来实现该需求：

SELECT USERID, 
       CASE
           WHEN TO_CHAR(CHECKIN, 'HH24') BETWEEN '08' AND '09' 
                OR TO_CHAR(CHECKIN, 'HH24') BETWEEN '16' AND '17' AND TO_CHAR(CHECKIN, 'MI') >= '30'
           THEN 
                CASE 
                    WHEN CHECKIN = (SELECT MIN(CHECKIN) 
                                    FROM T202303 t2 
                                    WHERE t1.USERID = t2.USERID 
                                        AND TO_CHAR(t2.CHECKIN, 'YYYY-MM-DD') = TO_CHAR(t1.CHECKIN, 'YYYY-MM-DD') 
                                        AND TO_CHAR(t2.CHECKIN, 'HH24') BETWEEN '08' AND '09' 
                                            OR TO_CHAR(t2.CHECKIN, 'HH24') BETWEEN '16' AND '17' 
                                                AND TO_CHAR(t2.CHECKIN, 'MI') >= '30')
                    THEN '有效'
                    ELSE '重复'
                END
           ELSE '无效'
       END AS STATUS,
       CHECKIN
FROM T202303 t1
ORDER BY USERID, CHECKIN;

该 SQL 语句中，首先判断 CHECKIN 是否在指定的时间段内，如果是，则使用子查询找到该用户在同一天内的最早打卡记录，如果该记录与当前记录相同，则视为“有效”，否则视为“重复”；如果不在指定的时间段内，则视为“无效”。其中，TO_CHAR 函数用于将日期时间转换为字符串，从而方便比较时间。