在sql里面 用case when 判定就行

with t1 as (

SELECT '张三' employee_name,'2025-09-22 12:00:00' start_time,'2025-09-23 01:00:00' end_time,'' deduct_hours UNION ALL

SELECT '李四' employee_name,'2025-09-22 12:00:00' start_time,'2025-09-23 01:00:00' end_time,'' deduct_hours UNION ALL

SELECT '王五' employee_name,'2025-09-22 10:00:00' start_time,'2025-09-23 16:20:00' end_time,'' deduct_hours UNION ALL

SELECT '赵六' employee_name,'2025-09-22 10:00:00' start_time,'2025-09-23 22:40:00' end_time,'' deduct_hours

)

SELECT 

    employee_name,

    start_time,

    end_time,

    CASE 

        -- 情况1：上班时间 < 10:30 且 10:30 < 下班时间 <= 16:30

        WHEN TIME(start_time) < '10:30:00' 

             AND TIME(end_time) > '10:30:00' 

             AND TIME(end_time) <= '16:30:00' 

        THEN 0.5

        -- 情况2：上班时间 > 10:30 且 16:30 < 下班时间 <= 22:30

        WHEN TIME(start_time) > '10:30:00' 

             AND TIME(end_time) > '16:30:00' 

             AND TIME(end_time) <= '22:30:00' 

        THEN 0.5

        -- 情况3：上班时间 < 10:30 且 下班时间 > 22:30

        WHEN TIME(start_time) < '10:30:00' 

             AND TIME(end_time) > '22:30:00' 

        THEN 1.5

        -- 其他情况

        ELSE 1

    END AS deduct_hours

FROM t1;