Oracle SQL递归查询实现：员工每日参与人数统计求助

需求拆解 #

核心统计逻辑：

• 仅纳入首日（最早日期）即参与的员工，中途加入的（如A8）直接排除
• 仅统计那些从首日到当前统计日期，每一天都有参与记录的员工，中途断档的员工不计入后续日期的统计
• 最终按日期输出每日符合条件的员工数量

方案一：递归CTE实现 #

递归CTE适合处理这种依赖连续日期的统计场景，步骤如下：

完整SQL代码 #

WITH dates AS (
    -- 提取所有唯一日期并按顺序编号
    SELECT DISTINCT emp_date,
           ROW_NUMBER() OVER (ORDER BY emp_date) AS rn
    FROM employee
),
first_day_emps AS (
    -- 筛选首日参与的所有员工ID（去重）
    SELECT DISTINCT emp_id
    FROM employee
    WHERE emp_date = (SELECT MIN(emp_date) FROM employee)
),
recursive_stats (current_date, count_participated, rn) AS (
    -- 递归基例：首日的统计结果
    SELECT d.emp_date,
           (SELECT COUNT(*) FROM first_day_emps),
           d.rn
    FROM dates d
    WHERE d.rn = 1
    UNION ALL
    -- 递归步骤：统计当前日期仍保持连续参与的员工数
    SELECT d.emp_date,
           (SELECT COUNT(*)
            FROM first_day_emps f
            -- 当天有参与记录
            WHERE EXISTS (SELECT 1 FROM employee e WHERE e.emp_id = f.emp_id AND e.emp_date = d.emp_date)
            -- 前一天也在统计列表中（保证连续性）
            AND EXISTS (SELECT 1 FROM recursive_stats rs_prev WHERE rs_prev.rn = d.rn -1 AND EXISTS (SELECT 1 FROM first_day_emps f_prev WHERE f_prev.emp_id = f.emp_id))
           ),
           d.rn
    FROM dates d
    JOIN recursive_stats rs ON d.rn = rs.rn + 1
)
SELECT TO_CHAR(current_date, 'DD-MM-YYYY') AS emp_date,
       count_participated
FROM recursive_stats
ORDER BY current_date;

递归逻辑说明 #

• dates CTE：整理所有唯一日期并编号，确保递归按时间顺序处理
• first_day_emps CTE：锁定首日参与的员工群体，排除中途加入者
• recursive_stats CTE：
  • 基例：直接输出首日的员工总数（5人）
  • 递归迭代：对每个后续日期，仅保留前一天仍在统计列表中且当天有参与记录的员工，以此保证连续参与的要求

方案二：非递归高效实现（推荐） #

递归虽直观，但大数据量下效率有限，用分组统计+窗口函数的方式更简洁高效：

完整SQL代码 #

WITH first_day AS (
    SELECT MIN(emp_date) AS min_date FROM employee
),
employee_dates AS (
    -- 去重每个员工的参与日期，避免同一天多条记录干扰
    SELECT DISTINCT emp_id, emp_date
    FROM employee
),
eligible_emps AS (
    SELECT ed.emp_id,
           ed.emp_date,
           -- 计算首日到当前日期的天数差
           TRUNC(ed.emp_date) - TRUNC((SELECT min_date FROM first_day)) AS day_diff,
           -- 给员工的参与日期按顺序编号
           ROW_NUMBER() OVER (PARTITION BY ed.emp_id ORDER BY ed.emp_date) AS emp_day_num
    FROM employee_dates ed
    -- 仅保留首日参与的员工
    WHERE EXISTS (
        SELECT 1 FROM employee_dates ed_first WHERE ed_first.emp_id = ed.emp_id AND ed_first.emp_date = (SELECT min_date FROM first_day)
    )
)
SELECT TO_CHAR(ed.emp_date, 'DD-MM-YYYY') AS emp_date,
       COUNT(DISTINCT ee.emp_id) AS count_participated
FROM employee_dates ed
JOIN eligible_emps ee ON ed.emp_date = ee.emp_date
-- 连续参与的员工，天数差等于参与次数编号减1（首日：0=1-1，次日：1=2-1，以此类推）
WHERE ee.day_diff = ee.emp_day_num - 1
GROUP BY ed.emp_date
ORDER BY ed.emp_date;

非递归逻辑说明 #

• first_day CTE：获取统计的起始日期
• employee_dates CTE：去重员工的参与日期，确保每个员工每天仅一条记录
• eligible_emps CTE：
  • 筛选首日参与的员工
  • 通过day_diff和emp_day_num的匹配关系，判断员工是否连续参与
• 最后按日期分组，统计符合连续条件的员工数量

验证结果 #

两种方案均会输出符合预期的结果：

emp_date   count_participated 
01-04-2024 5  
02-04-2024 3
03-04-2024 2
04-04-2024 1

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