Oracle使用sysdate-1查询连续工作超2天的项目分配用户

涉及表结构说明 #

• PJASSIGN表：存储项目用户实际工作记录
  • 字段：PPRJECT（项目标识，样例数据覆盖MSFT、GOOGLE、TSLA等项目）、LONINUSER（用户登录账号，需求中标准字段名为LOGINUSER）、DATE（工作日期，Oracle中DATE为关键字，实际使用需加双引号转义）
• USERINFO表：存储用户项目有效分配关系
  • 字段：USER_PJ_ID（分配记录唯一ID）、LONINUSER（用户登录账号）、PJASSIGN（关联对应项目标识）

筛选规则 #

• 日期统计基准使用sysdate-1语法，以自然日维度计算
• 规则1：用户必须在USERINFO表存在对应项目的有效分配，直接排除无关联项目分配的用户
• 规则2：用户在所属分配项目上，截止到统计基准日的连续工作时长超过2天
• 最终结果仅返回符合条件的LOGINUSER字段，预期返回值为Ken、Gary

初始SQL问题 #

给出的初始片段存在语法缺失，未完成表关联、日期校验、连续天数计算核心逻辑：

Select LOGINUSER
From PJASSIGN
where (sysdate- 1,'yyyy-mm-dd HH24:MI:SS' )

优化后可直接运行的完整SQL #

SELECT DISTINCT LOGINUSER
FROM (
    SELECT 
        p.LOGINUSER,
        COUNT(*) AS continuous_days
    FROM (
        SELECT 
            LOGINUSER,
            PPRJECT,
            TRUNC("DATE") AS work_dt,
            -- 同用户同项目下，日期减排序序号得到连续工作段的分组标识
            TRUNC("DATE") - ROW_NUMBER() OVER (
                PARTITION BY LOGINUSER, PPRJECT 
                ORDER BY TRUNC("DATE")
            ) AS continuous_group
        FROM PJASSIGN
        -- 仅统计sysdate-1及之前的有效工作记录
        WHERE TRUNC("DATE") <= TRUNC(SYSDATE - 1)
    ) p
    -- 内连接用户分配表，直接过滤无有效项目分配的用户
    INNER JOIN USERINFO u 
        ON p.LOGINUSER = u.LONINUSER 
        AND p.PPRJECT = u.PJASSIGN
    GROUP BY p.LOGINUSER, p.PPRJECT, continuous_group
    HAVING 
        -- 连续工作时长超2天即至少连续3天
        COUNT(*) > 2
        -- 校验连续段截止到sysdate-1，排除历史连续但近期断档的记录
        AND MAX(p.work_dt) = TRUNC(SYSDATE - 1)
) res

核心逻辑说明 #

• 所有日期处理统一用TRUNC()截断时分秒，保证按自然日计算，符合sysdate-1的语法要求
• 用ROW_NUMBER()窗口函数配合日期差值做连续分组，是Oracle场景下计算连续打卡/工作天数的通用高效方案
• 内连接USERINFO表直接完成第一条规则的校验，不需要额外写子查询过滤
• 最外层加DISTINCT去重，避免同一用户在多个项目满足条件时重复返回

如果实际业务中DATE字段存储带时分秒的时间戳，不需要修改逻辑，TRUNC函数会自动截断到日期维度；如果字段拼写和实际库有差异，直接替换SQL中对应字段名即可。

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