Oracle SQL存储过程问题：获取各RollNumber起始日前一行及起止数据

问题根源 #

原存储过程中，获取起始日期前一条记录的子查询dat3未按rollnumber和ec进行分区，仅做全局排序取第一条。当inRollNumber为空时，只会返回全表中log_datetime < inStartDate的单条最新记录，而非每个RollNumber+EC组合对应的前置记录，导致全量查询时丢失各组的前置数据。

修复后的完整存储过程 #

PROCEDURE spGetReportData
    (inEC varchar2 Default null,
     inRollNumber varchar2  Default null,
     inUserID varchar2  Default null,
     inStartDate varchar2  Default null,
     inEndDate varchar2 Default null,
     inStatus varchar2,
     inInternalStatus varchar2 Default null,
     outcursor out CURSOR_REF)
AS
BEGIN

open outcursor for
select  Result.*,Ut.FIRSTNAME ||' '||Ut.LASTNAME UserName,
CASE
                WHEN ROW_NUMBER() OVER (PARTITION BY Result.rollnumber,Result.ec ORDER BY Result.ActivityDate ASC) > 2
                THEN Result.rollnumber || '-' || Result.ec || '-' || (ROW_NUMBER() OVER (PARTITION BY Result.rollnumber,Result.ec ORDER BY Result.ActivityDate ASC) - 2) 
                ELSE Result.rollnumber || '-' || Result.ec 
            END UniqueId
from
  (
  -- 起止日期范围内的主数据
  select DAT1.*,
  dat1.log_datetime ActivityDate
  from TBLIRGLOG_TRANSACTION_DATA dat1
  where
        dat1.status=inStatus 
        AND DAT1.INTERNALSTATUS IN ('AC','CLIP', 'CLA4')
        and   (dat1.USERID =nvl(inUserID,dat1.USERID) or dat1.USERID IS NULL)
        and   (dat1.rollnumber =nvl(inRollNumber,dat1.rollnumber) or dat1.rollnumber IS NULL)
        and   (dat1.LOG_DATETIME >= nvl(inStartDate,dat1.LOG_DATETIME) OR dat1.LOG_DATETIME IS NULL)
        and   (dat1.LOG_DATETIME <= nvl(inEndDate,dat1.LOG_DATETIME) OR dat1.LOG_DATETIME IS NULL)

  UNION

  -- 每个RollNumber+EC组合的前置记录（起始日期前的最新一条）
  select dat2.*,dat2.log_datetime ActivityDate
  from TBLIRGLOG_TRANSACTION_DATA dat2
  inner join
  (
      SELECT rollnumber, ec, status, internalStatus, log_datetime
      FROM (
          SELECT
              rollnumber,
              ec,
              status,
              internalStatus,
              log_datetime,
              -- 核心修改：按RollNumber+EC分区，取组内最新记录
              ROW_NUMBER() OVER (PARTITION BY rollnumber, ec ORDER BY log_datetime DESC) AS rn
          FROM TBLIRGLOG_TRANSACTION_DATA
          WHERE
              log_datetime < inStartDate 
              AND status = inStatus
              AND internalStatus IN ('Admit','Enroll')
              AND (rollnumber = NVL(inRollNumber, rollnumber) OR NVL(inRollNumber, rollnumber) IS NULL)
              AND (ec = NVL(inEC, ec) OR NVL(inEC, ec) IS NULL)
              AND (USERID = NVL(inUserID, USERID) OR USERID IS NULL)
      )
      WHERE rn = 1
  ) dat3
  on dat2.rollnumber = dat3.rollnumber 
  and dat2.ec = dat3.ec 
  and dat2.status = dat3.status 
  and dat2.log_datetime = dat3.log_datetime
  where
        dat2.status= inStatus 
        AND DAT2.INTERNALSTATUS IN ('Admit','Enroll')

  )Result

left outer join TBLGUIUSER UT on Result.USERID = UT.USER_ID

where (Result.rollnumber=nvl(inRollNumber,Result.rollnumber))
and( Result.ec=nvl(inEC,Result.ec) OR Result.ec IS NULL)
and (Result.USERID =nvl(inUserID,Result.USERID) OR Result.USERID IS NULL)
and (Result.ActivityDate <= nvl(inEndDate,Result.ActivityDate) OR Result.ActivityDate IS NULL)

ORDER BY UniqueId, Result.ActivityDate ASC;

end spGetReportData;

关键修改说明 #

• 分区排序调整：将子查询dat3的排序逻辑从全局排序改为PARTITION BY rollnumber, ec的分组排序，确保每个RollNumber+EC组合都能单独筛选出起始日期前的最新记录。
• 过滤条件对齐：在前置记录的筛选子查询中补充了status、internalStatus、ec、USERID的过滤条件，确保前置记录的筛选逻辑与主查询一致，避免返回无关数据。
• 逻辑严谨性优化：移除了原代码中冗余的条件判断，保证全量查询和单个RollNumber查询的逻辑统一。

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