如何用SQL为单个员工存储多个假期日期范围并支持日期查询？

1. 表结构设计 #

把员工基础信息和假期记录拆分到两张表，既避免数据冗余，又能支持单个员工的多个假期范围存储：

员工表（employees） #

存储员工核心信息，每条记录对应一位员工：

CREATE TABLE employees (
    employeeID INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    PRIMARY KEY (employeeID)
);

假期记录表（employee_vacations） #

存储每个员工的假期日期范围，一条记录对应一个假期段（单日假期只需将开始和结束日期设为同一天）：

CREATE TABLE employee_vacations (
    vacationID INT NOT NULL AUTO_INCREMENT,
    employeeID INT NOT NULL,
    vacation_start DATE NOT NULL,
    vacation_end DATE NOT NULL,
    PRIMARY KEY (vacationID),
    FOREIGN KEY (employeeID) REFERENCES employees(employeeID)
        ON DELETE CASCADE ON UPDATE CASCADE
);

外键关联可保证员工信息删除时，对应的假期记录同步删除，避免产生脏数据

2. 插入示例数据 #

按照你提供的员工假期信息，插入数据的示例如下：

先插入员工基础信息 #

INSERT INTO employees (Name, Email) VALUES
('Jeff', 'jeff@example.com'),
('Molly', 'molly@example.com'),
('Jane', 'jane@example.com');

再插入假期范围记录 #

-- Jeff的两个假期
INSERT INTO employee_vacations (employeeID, vacation_start, vacation_end) VALUES
(1, '2024-02-13', '2024-02-19'),
(1, '2024-12-05', '2024-12-07');

-- Molly的单日假期
INSERT INTO employee_vacations (employeeID, vacation_start, vacation_end) VALUES
(2, '2024-05-03', '2024-05-03');

-- Jane的三个假期
INSERT INTO employee_vacations (employeeID, vacation_start, vacation_end) VALUES
(3, '2024-01-05', '2024-01-10'),
(3, '2024-07-19', '2024-08-24'),
(3, '2024-11-14', '2024-11-23');

3. 按日期查询休假员工 #

比如查询2024年7月20日当天休假的所有员工，通过关联两张表并判断日期是否在假期范围内即可：

SELECT e.Name, e.Email
FROM employees e
JOIN employee_vacations v ON e.employeeID = v.employeeID
WHERE '2024-07-20' BETWEEN v.vacation_start AND v.vacation_end;

这条语句会返回Jane，因为她的7/19-8/24假期包含7/20当天。

设计优势 #

• 无数据冗余：员工基础信息仅存储一次，无需在每条假期记录中重复保存
• 扩展性强：支持任意数量的假期范围，新增/删除假期只需操作假期表
• 查询高效：通过外键关联和日期范围判断，可快速定位目标员工

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