如何查询SQL表中当日缺勤且连续3天符合规则的员工

Alright, let's break down how to solve this SQL problem. First, let's clarify the context with the provided data and requirements:

Original Table Data #

Here's the structured view of your employee attendance table:

Status Code Definitions #

• A = Absent (缺勤)
• P = Present (出勤)
• H = Holiday (节假日)
• W = Weak Off (休息日)

Query Requirements #

We need to find employees who meet both of these criteria:

1. Their DayStatus on the latest date (20-May-2018) is A.
2. They have a continuous 3-day period where only P breaks the streak—H and W do not interrupt the "absent count".

Expected Output #

Code Name
101 John
105 Milton
107 Saleh
108 Virat

SQL Solution #

We can use window functions to group consecutive non-present days and check the required conditions. Here's the query:

WITH employee_status_groups AS (
    SELECT 
        Code,
        Name,
        DayStatus,
        Date,
        -- Create a group ID that increments only when we hit a 'P' (present)
        SUM(CASE WHEN DayStatus = 'P' THEN 1 ELSE 0 END) OVER (
            PARTITION BY Code 
            ORDER BY TO_DATE(Date, 'DD-Mon-YYYY') DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS streak_group
    FROM your_table_name
),
valid_streaks AS (
    SELECT 
        Code,
        Name,
        COUNT(*) AS consecutive_days,
        -- Check if this streak includes the latest date with status 'A'
        MAX(CASE 
            WHEN TO_DATE(Date, 'DD-Mon-YYYY') = TO_DATE('20-May-2018', 'DD-Mon-YYYY') 
            AND DayStatus = 'A' THEN 1 
            ELSE 0 
        END) AS has_valid_latest_day
    FROM employee_status_groups
    GROUP BY Code, Name, streak_group
    HAVING COUNT(*) >= 3
)
SELECT DISTINCT Code, Name
FROM valid_streaks
WHERE has_valid_latest_day = 1
ORDER BY Code;

How This Works #

1. CTE 1: employee_status_groups
   We partition the data by each employee (Code) and order their records by date descending. Using a running total, we assign a streak_group ID—every time we encounter a P, the group ID increases. This groups together all consecutive days where the employee wasn't present (A/H/W).

2. CTE 2: valid_streaks
   We group by employee and streak group, counting the number of days in each streak. We also check if the streak includes the latest date (20-May-2018) with status A. We filter streaks that are at least 3 days long.

3. Final Select
   We pick distinct employees who have at least one valid streak and meet the latest date condition, then sort by Code.

Note: Adjust the date conversion function based on your database:

• For MySQL use STR_TO_DATE(Date, '%d-%b-%Y')
• For SQL Server use CONVERT(DATE, Date, 106)

内容的提问来源于stack exchange，提问作者Abdullah Al Mamun