SQL实现：判断前后日期并统计休息日前后病假次数

Hey there! Let's tackle this problem where we need to count how many times a rest day has a sick day either the day before or after it. From your sample data, the answer should be 2—let's break down how to get there with SQL.

First, let's recap the requirement clearly: we need to count every rest date where either the previous day OR the next day has a sick event. In your example:

• 2015-01-03 (rest) has a sick day the next day (2015-01-04) → counts as 1
• 2015-01-07 (rest) has a sick day the day before (2015-01-06) → counts as 1
  Total: 2, which matches what you expect.

The key here is figuring out how to check if two dates are consecutive (one day before/after). Below are a couple of solid SQL approaches that work across most databases, with notes on adjusting for specific systems.

Approach 1: Self-Join to Match Adjacent Dates #

We can join the table to itself to link each rest day with any sick days that fall right before or after it, then count the valid rest days:

-- MySQL example (adjust date functions for your database)
SELECT COUNT(DISTINCT rest_days.Date) AS adjacent_sick_count
FROM your_table rest_days
LEFT JOIN your_table sick_days
  ON (sick_days.Date = DATE_ADD(rest_days.Date, INTERVAL 1 DAY) 
      OR sick_days.Date = DATE_SUB(rest_days.Date, INTERVAL 1 DAY))
  AND sick_days.Event = 'sick'
WHERE rest_days.Event = 'rest'
  AND sick_days.Date IS NOT NULL;

Approach 2: Use EXISTS to Check for Adjacent Sick Days #

If you prefer a subquery approach, EXISTS lets us check directly whether a rest day has a sick neighbor without joining:

-- PostgreSQL example (uses interval arithmetic)
SELECT COUNT(*) AS adjacent_sick_count
FROM your_table rest_days
WHERE rest_days.Event = 'rest'
  AND EXISTS (
    SELECT 1
    FROM your_table sick_days
    WHERE sick_days.Event = 'sick'
      AND (sick_days.Date = rest_days.Date + INTERVAL '1 DAY' 
           OR sick_days.Date = rest_days.Date - INTERVAL '1 DAY')
  );

Quick Notes for Different Databases #

• SQL Server: Replace date functions with DATEADD(day, 1, rest_days.Date) and DATEADD(day, -1, rest_days.Date)
• Oracle: Use rest_days.Date + 1 and rest_days.Date - 1 (since Oracle treats dates as numbers for arithmetic)

How This Works #

1. We start by filtering all rows where the event is rest
2. For each rest day, we check if there's a sick event on either the previous or next calendar day
3. We count each valid rest day once (even if both adjacent days are sick—COUNT(DISTINCT) in the join approach ensures we don't double-count)

Testing this with your sample data will give you the expected result of 2.

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