如何统计假期申请中需排除的公共假日天数？

问题说明 #

现有两张Oracle数据库表：

• XX_LEAVES_EXCLUDES：存储假期申请记录，包含exclude_id（主键）、holiday_start（假期开始日期）、holiday_end（假期结束日期）字段
• XX_LEAVES_PUBLIC_HOLIDAYS：存储公共假日信息，包含PUBLIC_HOLIDAY_UAE_YEAR_2022（假日名称）及HOLIDAY_DATE（假日日期）字段

需求：为XX_LEAVES_EXCLUDES表新增一列，统计每条假期申请时间段内包含的公共假日天数（即需从假期中排除的天数）。

示例：若某假期的holiday_start为09-Jul-2022、holiday_end为13-Jul-2022，且公共假日表存在10-Jul-2022的记录，则该申请的排除天数为1。

附建表及插入数据语句 #

-- 假期申请表
create table XX_LEAVES_EXCLUDES
(
exclude_id number not null primary key,
holiday_start date not null,
holiday_end date not null
);

create sequence seq_exclude_id MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 2;

create or replace trigger trg_exclude_id
before insert 
on XX_LEAVES_EXCLUDES
for each row
begin
:new.exclude_id:=seq_exclude_id.nextval;
end;

INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('23-Jul-2022','20-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('01-Jul-2022','02-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('13-Jul-2022','29-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('12-Jul-2022','01-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('01-Jul-2022','29-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('08-Jul-2022','08-Aug-2022');
INSERT INTO XX_LEAVES_EXCLUDES (HOLIDAY_START, HOLIDAY_END) VALUES ('03-Jul-2022','20-Aug-2022');

-- 公共假日表
CREATE TABLE  "XX_LEAVES_PUBLIC_HOLIDAYS" 
(    "PUBLIC_HOLIDAY_UAE_YEAR_2022" VARCHAR2(50) NOT NULL, 
    "HOLIDAY_DATE" DATE NOT NULL ENABLE
);

INSERT INTO XX_LEAVES_PUBLIC_HOLIDAYS (PUBLIC_HOLIDAY_UAE_YEAR_2022, HOLIDAY_DATE) VALUES ('National Day','10-Jul-2022');

实现步骤 #

1. 新增统计列 #

首先为XX_LEAVES_EXCLUDES表添加用于存储公共假日天数的列，默认值设为0：

ALTER TABLE XX_LEAVES_EXCLUDES
ADD exclude_holiday_days NUMBER DEFAULT 0;

2. 更新现有记录的统计值 #

通过关联公共假日表，统计每条假期申请时间段内的公共假日数量并更新到新增列：

UPDATE XX_LEAVES_EXCLUDES e
SET exclude_holiday_days = (
    SELECT COUNT(*)
    FROM XX_LEAVES_PUBLIC_HOLIDAYS h
    WHERE h.HOLIDAY_DATE BETWEEN e.holiday_start AND e.holiday_end
);

3. （可选）自动维护统计值 #

如果后续会新增假期记录或公共假日，可创建触发器自动更新该字段：

-- 插入假期记录时自动计算
CREATE OR REPLACE TRIGGER trg_update_exclude_days_insert
AFTER INSERT ON XX_LEAVES_EXCLUDES
FOR EACH ROW
BEGIN
    UPDATE XX_LEAVES_EXCLUDES e
    SET e.exclude_holiday_days = (
        SELECT COUNT(*)
        FROM XX_LEAVES_PUBLIC_HOLIDAYS h
        WHERE h.HOLIDAY_DATE BETWEEN e.holiday_start AND e.holiday_end
    )
    WHERE e.exclude_id = :NEW.exclude_id;
END;
/

-- 新增公共假日时更新所有受影响的假期记录
CREATE OR REPLACE TRIGGER trg_update_exclude_days_holiday
AFTER INSERT ON XX_LEAVES_PUBLIC_HOLIDAYS
FOR EACH ROW
BEGIN
    UPDATE XX_LEAVES_EXCLUDES e
    SET e.exclude_holiday_days = e.exclude_holiday_days + 1
    WHERE :NEW.HOLIDAY_DATE BETWEEN e.holiday_start AND e.holiday_end;
END;
/

验证结果 #

执行查询查看统计结果：

SELECT exclude_id, holiday_start, holiday_end, exclude_holiday_days
FROM XX_LEAVES_EXCLUDES;

对于包含10-Jul-2022的假期记录（比如holiday_start为01-Jul-2022、holiday_end为02-Aug-2022的记录），exclude_holiday_days值为1，其他不包含该日期的记录值为0。

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