找到符合以下需求的六次打卡，1，以每天首次打卡为基准，余下每次打卡时间间隔大于等于1小时，小于三小时，2，第五次打卡时间要小于18:00，第六次打卡时间要大于18:00

题目：从上述“已签到”表格内销售的n次打卡中找到符合以下需求的六次打卡，1，以每天首次打卡为基准，余下每次打卡时间间隔大于等于1小时，小于三小时，2，第五次打卡时间要小于18:00，第六次打卡时间要大于18:00
数据：链接：https://pan.baidu.com/s/1m3Pq8JNYdAGyY-zX8N9DYw
提取码：hu54

-- 视图
WITH `temp`
as (SELECT 
*, ROW_NUMBER() over(PARTITION by `NAME` ORDER BY time ASC) `rank`
FROM sign_in
) 
  
SELECT
  a.`name`
FROM 
temp a,
temp b,
temp c,
temp d,
temp e,
temp f
WHERE
a.`name` = b.`name`
AND b.`name` = c.`name`
AND c.`name` = d.`name`
AND d.`name` = e.`name`
AND e.`name` = f.`name`
-- 取前面连续6个
AND a.rank = 6
AND b.rank = 5
AND c.rank = 4
AND d.rank = 3
AND e.rank = 2
AND f.rank = 1
-- 时间条件
AND TIMESTAMPDIFF(HOUR, b.time, a.time) BETWEEN 1 and 3
AND TIMESTAMPDIFF(HOUR, c.time, b.time) BETWEEN 1 and 3
AND TIMESTAMPDIFF(HOUR, d.time, c.time) BETWEEN 1 and 3
AND TIMESTAMPDIFF(HOUR, e.time, d.time) BETWEEN 1 and 3
AND TIMESTAMPDIFF(HOUR, f.time, e.time) BETWEEN 1 and 3
AND TIMESTAMPDIFF(SECOND, b.time, '2024-04-19 18:00:00') >= 0
AND TIMESTAMPDIFF(SECOND, a.time, '2024-04-19 18:00:00') <= 0
GROUP BY `name`

或者

WITH RankedSignIns AS (
    SELECT 
        `name`,
        `time`,
        ROW_NUMBER() OVER (PARTITION BY `name`, DATE(`time`) ORDER BY `time`) AS `rank`
    FROM 
        sign_in
)

SELECT 
    a.`name`
FROM 
    RankedSignIns a
    JOIN RankedSignIns b ON a.`name` = b.`name` AND b.`rank` = a.`rank` - 1
    JOIN RankedSignIns c ON a.`name` = c.`name` AND c.`rank` = b.`rank` - 1
    JOIN RankedSignIns d ON a.`name` = d.`name` AND d.`rank` = c.`rank` - 1
    JOIN RankedSignIns e ON a.`name` = e.`name` AND e.`rank` = d.`rank` - 1
    JOIN RankedSignIns f ON a.`name` = f.`name` AND f.`rank` = e.`rank` - 1
WHERE
    a.`rank` = 6
    AND TIMESTAMPDIFF(HOUR, f.`time`, e.`time`) BETWEEN 1 AND 3
    AND TIMESTAMPDIFF(HOUR, e.`time`, d.`time`) BETWEEN 1 AND 3
    AND TIMESTAMPDIFF(HOUR, d.`time`, c.`time`) BETWEEN 1 AND 3
    AND TIMESTAMPDIFF(HOUR, c.`time`, b.`time`) BETWEEN 1 AND 3
    AND TIMESTAMPDIFF(HOUR, b.`time`, a.`time`) BETWEEN 1 AND 3
    AND TIME(b.`time`) < '18:00:00'
    AND TIME(a.`time`) > '18:00:00'
GROUP BY 
    a.`name`;