MySQL查询需求：员工考勤时长计算与月度汇总

Hey Adam, let's work through this attendance query problem together—here's how to get the exact results you need:

First, we need to correctly pair each employee's daily check-in (IN) and check-out (OUT) records, then calculate the required metrics and summaries.

1. Daily Attendance Details (with Stay Time & Global Stats) #

This query will return every employee's daily check-in/out times, their stay duration in minutes, plus the total stay minutes across all employees and that total minus your fixed value YYY:

SELECT
    a.id AS check_in_record_id,
    b.id AS check_out_record_id,
    a.PERSON,
    a.IN AS check_in_time,
    b.OUT AS check_out_time,
    TIMESTAMPDIFF(MINUTE, a.IN, b.OUT) AS stay_minutes,
    SUM(TIMESTAMPDIFF(MINUTE, a.IN, b.OUT)) OVER() AS total_stay_minutes,
    SUM(TIMESTAMPDIFF(MINUTE, a.IN, b.OUT)) OVER() - YYY AS total_minus_yyy
FROM presence a
INNER JOIN presence b 
    ON a.PERSON = b.PERSON
    AND DATE(a.IN) = DATE(b.OUT)  -- Match records from the same day
    AND a.IN < b.OUT  -- Ensure check-in time is before check-out
WHERE a.IN IS NOT NULL  -- Filter valid check-in records
  AND b.OUT IS NOT NULL  -- Filter valid check-out records
ORDER BY a.PERSON, a.IN;

Key Notes: #

• Replaced DATEDIFF with DATE(a.IN) = DATE(b.OUT) for clearer day-matching logic
• Used TIMESTAMPDIFF(MINUTE, ...) to directly calculate stay duration in minutes (no need to convert seconds manually)
• The window function SUM() OVER() calculates the total stay minutes across all rows without extra subqueries
• Replace YYY with your actual fixed numerical value

2. Monthly Stay Time Summary #

To aggregate total stay minutes per employee per month, use this query:

SELECT
    a.PERSON,
    DATE_FORMAT(a.IN, '%Y-%m') AS attendance_month,
    SUM(TIMESTAMPDIFF(MINUTE, a.IN, b.OUT)) AS monthly_stay_minutes
FROM presence a
INNER JOIN presence b 
    ON a.PERSON = b.PERSON
    AND DATE(a.IN) = DATE(b.OUT)
    AND a.IN < b.OUT
WHERE a.IN IS NOT NULL
  AND b.OUT IS NOT NULL
GROUP BY a.PERSON, DATE_FORMAT(a.IN, '%Y-%m')
ORDER BY attendance_month, a.PERSON;

Fixes for Your Original Query #

• Your original query only targeted Person A—the above queries include all employees
• Calculating seconds then subtracting a fixed value is less intuitive than using minute-based time difference directly
• Adding a.IN < b.OUT prevents invalid pairings (e.g., check-out time earlier than check-in)

内容的提问来源于stack exchange，提问作者Adam