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