Oracle 使用绑定变量查询满足工时要求的延期项目SQL写法

基础信息 #

PROJECT表结构与测试数据 #

PROJECT     PJID     STARTDYA       ENDDAY         STATUS        PJINC
Lakers      P01      03/01/2021     04/01/2021     SExceed       Ken
Lakers      P03      04/01/2021     05/01/2021     NonExceed     John
Lakers      P04      02/01/2021     03/01/2021     NonExceed     Simon
Bulls       P05      09/01/2021     10/01/2021     EExceed       Billy
Bulls       P06      07/01/2021     08/01/2021     EExceed       Gary
Heat        P07      08/01/2021     09/01/2021     SExceed       Tim
Heat        P08      05/01/2021     06/01/2021     NonExceed     Mary

EMPLOYEE表结构与测试数据 #

Project    PJINC      WORKDATE         WORKHRS
Lakers     Ken        02/28/2021       3
Lakers     Ken        02/28/2021       3
Lakers     Ken        02/28/2021       6
Lakers     John       03/28/2021       12
Lakers     Simon      04/01/2021       2
Bulls      Billy      09/30/2021       3
Bulls      Billy      09/30/2021       7
Bulls      Gary       07/30/2021       1
Heat       Tim        01/16/2021       3
Heat       Mary       05/31/2021       5

预期返回结果 #

PJINC     PROJECT      PJID
Ken       Lakers       P01
Billy     Bulls        P05

查询规则 #

• 延期日期判定：根据项目STATUS字段匹配校验逻辑，STATUS为SExceed时要求STARTDYA早于传入绑定变量:SETDATE；STATUS为EExceed时要求ENDDAY早于传入绑定变量:SETDATE；STATUS为NonExceed的项目直接排除。
• 工时校验规则：项目负责人（PJINC字段）需在校验基准日期的前1个自然日，单日累计WORKHRS≥10小时，其中SExceed的基准日期取STARTDYA，EExceed的基准日期取ENDDAY。

校验示例1：传入:SETDATE为10/31/2021，Ken所属Lakers项目状态为SExceed，STARTDYA03/01/2021早于传入日期；Ken在STARTDYA前1日02/28/2021累计工时3+3+6=12小时，满足≥10要求，符合输出条件。
校验示例2：Billy所属Bulls项目状态为EExceed，ENDDAY10/01/2021早于传入日期；Billy在ENDDAY前1日09/30/2021累计工时3+7=10小时，满足≥10要求，符合输出条件。

原有实现问题 #

原有初步SQL未满足全部要求，存在以下缺陷：

• 两表未做匹配关联，会产生笛卡尔积错误
• 未实现工时聚合校验逻辑
• 日期判断逻辑写在查询字段中，未作为过滤条件生效
• 硬编码测试日期，未使用绑定变量传参
  原有SQL如下：

SELECT PJINC, PROJECT, PJID,
CASE WHEN STATUS = 'SExceed' THEN
     TO_DATE('10/31/2021', 'MM/DD/YYYY') /* user set day should be here (bind variable)*/ > TO_DATE(STARTDYA, 'MM/DD/YYYY')
     WHEN STATUS = 'EExceed' THEN
     TO_DATE('10/31/2021', 'MM/DD/YYYY') /* user set day should be here (bind variable)*/ > TO_DATE(ENDDAY, 'MM/DD/YYYY')
END as DelayProject
FROM PROJECT, EMPLOYEE
Where STATUS = 'SExceed' or STATUS = 'EExceed'

正确实现SQL #

SELECT 
  p.PJINC, 
  p.PROJECT, 
  p.PJID
FROM PROJECT p
LEFT JOIN EMPLOYEE e
  ON p.PROJECT = e.Project
  AND p.PJINC = e.PJINC
  -- 直接关联基准日期前1天的工时记录，减少无效数据扫描
  AND e.WORKDATE = TO_CHAR(
    CASE p.STATUS
      WHEN 'SExceed' THEN TO_DATE(p.STARTDYA, 'MM/DD/YYYY') - 1
      WHEN 'EExceed' THEN TO_DATE(p.ENDDAY, 'MM/DD/YYYY') - 1
    END,
    'MM/DD/YYYY'
  )
WHERE
  p.STATUS IN ('SExceed', 'EExceed')
  -- 延期日期规则校验
  AND CASE p.STATUS
        WHEN 'SExceed' THEN TO_DATE(p.STARTDYA, 'MM/DD/YYYY')
        WHEN 'EExceed' THEN TO_DATE(p.ENDDAY, 'MM/DD/YYYY')
      END < :SETDATE
GROUP BY p.PJINC, p.PROJECT, p.PJID
-- 聚合校验单日累计工时≥10
HAVING SUM(NVL(e.WORKHRS, 0)) >= 10;

实现说明 #

• 全程使用绑定变量:SETDATE接收传入日期参数，符合要求
• 先过滤PROJECT表中不符合状态、延期日期规则的记录，降低关联计算量
• 关联EMPLOYEE表时直接匹配校验日期前1天的工时记录，避免全表扫描无效数据
• 用GROUP BY+ HAVING实现单日工时聚合校验，NVL兼容无对应工时记录的空值场景
• 传入:SETDATE=TO_DATE('10/31/2021','MM/DD/YYYY')测试，返回结果与预期完全一致。

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