主页 / coding / 问题 / 78900160
Accepted
hud
Asked: 2024-08-22 15:02:26 +0800 CST

从主表 Oracle 过程获取子表的 jobid 的最大 progressid

  • 772

我有 2 个表，分别名为TBL_FIBER_INV_JOBS和TBL_FIBER_INV_JOB_PROGRESS。以下是这两个表的描述

TBL_FIBER_INV_JOBS (Master Table) & TBL_FIBER_INV_JOB_PROGRESS (Child Table)

TBL_FIBER_INV_JOBS (Master Table)
Name                      Null?    Type           
------------------------- -------- -------------- 
JOB_ID                    NOT NULL NUMBER         
SPAN_ID                            NVARCHAR2(100) 
LINK_ID                            NVARCHAR2(100) 
CREATED_BY                         NVARCHAR2(200) 
CREATED_DATE              NOT NULL DATE           
MAINTENANCEZONECODE                NVARCHAR2(50)  
MAINTENANCEZONENAME                NVARCHAR2(100) 
MAINT_ZONE_NE_SPAN_LENGTH          NUMBER(10,4)   
SPAN_TYPE                          NVARCHAR2(20)  
JOB_FLAG                           NUMBER         
MISSING_ABD_LENGTH                 NUMBER(38,10)  
REOFFERFLAG                        VARCHAR2(10)   


TBL_FIBER_INV_JOB_PROGRESS (Child Table)
Name                   Null?    Type           
---------------------- -------- -------------- 
JOB_PROGRESS_ID        NOT NULL NUMBER         
JOB_ID                          NUMBER         
STATUS_ID                       NUMBER 
APPROVED_BY                     NVARCHAR2(200) 
APPROVED_DATE                   DATE           
REJECTED_BY                     NVARCHAR2(200) 
REJECTED_DATE                   DATE           
APPROV_REJECT_REMARK            NVARCHAR2(255) 
DELAY_REASON                    NVARCHAR2(255) 
ISABDMISSING                    NUMBER         
HOTO_OFFERED_LENGTH             NUMBER         
LIT_OFFERED_LENGTH              NUMBER         
HOTO_ACTUAL_LENGTH              NUMBER         
LIT_ACTUAL_LENGTH               NUMBER         
ABD_COMPLETED_LENGTH            NUMBER         
NE_SPAN_LENGTH                  NUMBER(10,4)   
CREATED_BY                      NVARCHAR2(200) 
CREATED_DATE           NOT NULL DATE           
MODIFIED_BY                     NVARCHAR2(200) 
MODIFIED_DATE                   DATE           
UMS_GROUP_ASS_BY_ID             NUMBER         
UMS_GROUP_ASS_BY_NAME           NVARCHAR2(200) 
UMS_GROUP_ASS_TO_ID             NUMBER         
UMS_GROUP_ASS_TO_NAME           NVARCHAR2(200) 
UMS_GROUP_ASS_TO_DATE           DATE           
JOB_PROGRESS_FLAG      NOT NULL NUMBER

因此情况是JOB_ID子表中可以有多个具有唯一性的JOB_PROGESS_ID。现在我想要的是

JOB_PROGESS_ID我总是希望在子表的一个条件下获得最大值的过程JOB_ID是

子表的条件：-UMS_GROUP_ASS_BY_NAME = 'CMM'和UMS_GROUP_ASS_TO_NAME IS NULL。我只想要主表中的 3 列，即JOB_ID, SPAN_ID, LINK_ID

由于我无法获取JOB_PROGRESS_ID特定 的最大值JOB_ID。请提出建议

更新

我尝试了以下查询，但它并不准确。

select max(job_progress_id),hoto_actual_length from tbl_fiber_inv_job_progress where job_id = 86753 and ums_group_ass_by_name='CMM' and ums_group_ass_to_name is null group by hoto_actual_length;

select max(job_id), span_id, job_flag,nvl(missing_abd_length,0) missing_abd_length, maint_zone_ne_span_length, maintenancezonecode from tbl_fiber_inv_jobs where job_id = 86753 and job_flag = 0 and span_type <> 'FTTX' group by span_id, job_flag,missing_abd_length, maint_zone_ne_span_length,maintenancezonecode; -- 187375

oracle

1 个回答

  1. Best Answer

    它不是连接（连同聚合和一些过滤器）吗？

      SELECT j.job_id,
         j.span_id,
         j.link_id,
         MAX (p.job_progress_id) job_progress_id
    FROM tbl_fiber_inv_jobs j JOIN tbl_fiber_inv_job_progress p ON p.job_id = j.job_id
   WHERE     p.ums_group_ass_by_name = 'CMM'
         AND p.ums_group_ass_to_name IS NULL
GROUP BY j.job_id, j.span_id, j.link_id;

    或者，也许使用 CTE？

    WITH
   temp
   AS
      -- get MAX job_progress_id for every job_id (filter applied)
      (  SELECT MAX (p.job_progress_id) job_progress_id, p.job_id
           FROM tbl_fiber_inv_job_progress p
          WHERE     p.ums_group_ass_by_name = 'CMM'
                AND p.ums_group_ass_to_name IS NULL
       GROUP BY p.job_id)
-- get columns from the master table joined with the temp CTE on a common column (job_id)
SELECT j.job_id,
       j.span_id,
       j.link_id,
       t.job_progress_id
  FROM tbl_fiber_inv_jobs j JOIN temp t ON t.job_id = j.job_id;

    [编辑]您评论说您需要另一个 (历史记录) 表中的数据。我的理解如下：

    WITH
   temp
   AS
      -- get MAX job_progress_id for every job_id (filter applied).
      -- for ne_span_length, join history with progress table on common columns
      (  SELECT p.job_id,
                MAX (p.job_progress_id) job_progress_id,
                MAX (GREATEST (p.ne_span_length, h.ne_span_length)) ne_span_length
           FROM tbl_fiber_inv_job_progress p
                JOIN tbl_fiber_inv_job_progress_h
                   ON     h.job_id = p.job_id
                      AND ...                --> additional join columns, if any
          WHERE     p.ums_group_ass_by_name = 'CMM'
                AND p.ums_group_ass_to_name IS NULL
       GROUP BY p.job_id)
-- get columns from the master table joined with the temp CTE on a common column (job_id)
SELECT j.job_id,
       j.span_id,
       j.link_id,
       t.job_progress_id,
       t.ne_span_length
  FROM tbl_fiber_inv_jobs j JOIN temp t ON t.job_id = j.job_id;
    • 1

相关问题