使用oracle sql查找最后完成任务

Question

我需要在每个组中使用sql找到最后一个完成任务。我有两个条件。construction_type = 0和construction_type in (1, 2).为了construction_type = 0。只有已完成的任务才会被取走。但对construction_type in (1, 2)来说。可以获取已完成和未完成的任务。我进行了查询，但问题是，如果任务已经完成，而一些新的任务输入没有完成，那么这个未完成的任务就会出现在顶部。我尝试了不同的顺序，但在construction_type in (1, 2)的情况下，不知何故无法得到最后完成的任务。对于construction_type = 0，没有问题，因为只有完成的任务才会被获取。

我是通过task_last_modified_date订购的。如果某个新任务将进入，那么它具有最高的task_last_modified_date。这就是为什么我还没完成任务的原因。它应该类似于latest task_last_modified_date with completed task。但我不知道该怎么做。

以下是示例数据

CREATE TABLE project_complete_data (CONSTRUCTION_TYPE, PROJECT_ID, PROJ_COMPANY_NAME,   CUSTOM_LOT_NO, LOT_STATUS, SCHEDULE_TASK_ID, TASK_NAME, TASK_STATUS, TASK_ACTUAL_END_DATE, TASK_FINISH_DATE, TASK_FINISHED, TASK_LAST_MODIFIED_DATE, PO_TOTAL_AMOUNT, PO_STATUS, PO_TYPE) AS
          SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961162, 'electrical rough',                     101, DATE '2021-12-13', DATE '2021-11-19', 0, TO_DATE('2021-11-16 14:44:04', 'YYYY-MM-DD hh24:mi:ss'),    49.7, 100, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961161, 'plumbing rough',                       101, DATE '2021-12-06', DATE '2021-11-12', 1, TO_DATE('2021-11-15 15:22:13', 'YYYY-MM-DD hh24:mi:ss'),    0.3,  101, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961159, 'roof framing',                         101, DATE '2021-11-29', DATE '2021-11-15', 1, TO_DATE('2021-11-15 13:04:37', 'YYYY-MM-DD hh24:mi:ss'),    10,   101, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961158, 'second floor framing',                 101, DATE '2021-11-15', DATE '2021-11-09', 1, TO_DATE('2021-11-09 15:22:42', 'YYYY-MM-DD hh24:mi:ss'),    50,   101, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961157, 'first floor framing',                  101, DATE '2021-11-08', DATE '2021-11-09', 1, TO_DATE('2021-11-09 14:44:57', 'YYYY-MM-DD hh24:mi:ss'),    0,    101, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '0000000077', 102, 10961157, 'first floor framing',                  101, DATE '2021-11-08', DATE '2021-11-09', 1, TO_DATE('2021-11-09 14:44:57', 'YYYY-MM-DD hh24:mi:ss'),    0,    101, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '01a',        103, 10134563, 'Rough-In Plumbing',                    101, DATE '2009-12-17', DATE '2014-09-01', 1, NULL,                                                       0,    100, 100  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '02',         103, 2213016,  'Update Lender Info in BT - Sales Rep', 101, DATE '2005-06-28', DATE '2005-10-11', 1, NULL,                                                       0,    100, 100  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '020',        103, 10955239, 'TK task',                              101, DATE '2018-11-07', DATE '2019-06-21', 1, TO_DATE('2019-06-21 18:12:21', 'YYYY-MM-DD hh24:mi:ss'),    0,    100, 101  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', '020',        103, 10955239, 'TK task',                              101, DATE '2018-11-07', DATE '2019-06-21', 1, TO_DATE('2019-06-21 18:12:21', 'YYYY-MM-DD hh24:mi:ss'),    33,   100, 100  FROM DUAL
UNION ALL SELECT 1, 511, 'Amber Hills Inc.', 'testpa01',   102, 10959716, 'HVAC rough',                           101, DATE '2021-09-17', DATE '2021-09-15', 0, TO_DATE('2021-11-02 12:28:57', 'YYYY-MM-DD hh24:mi:ss'),    0,    101, 100  FROM DUAL;

以下是查询

select 
    proj_company_name, 
    custom_lot_no,
    task_name,
    task_actual_end_date,                           
    task_finish_date,
    task_finished,
    task_last_modified_date,
    to_char(task_last_modified_date, 'YYYY-MM-DD hh24:mi:ss') AS TASK_LAST_MODIFIED_DATE_TIME,
    ROW_NUMBER() OVER (PARTITION BY custom_lot_no ORDER BY to_char(task_last_modified_date, 'YYYY-MM-DD hh24:mi:ss') desc) AS LAST_TASK_FINISH_SEQ,
    po_total_amount,
    po_status,
    CASE 
        WHEN po_status = 101 THEN po_total_amount   
        ELSE 0
    END as POs_COMPLETED,
    po_type,
    CASE 
        WHEN po_type = 100 THEN po_total_amount 
        ELSE 0
    END as "COMMITTED"
FROM project_complete_data
where project_id = 511
AND po_status in ('100', '101')
AND lot_status in ('102', '103')
And task_status = 101
AND (
        (construction_type = 0 AND task_finished = 1 and (schedule_task_id is not null or schedule_task_id > 0))    
        OR (construction_type in ('1', '2') AND task_finished in ('1', '0'))                
) 
order by custom_lot_no, to_char(task_last_modified_date, 'YYYY-MM-DD hh24:mi:ss') desc;

此查询提供以下结果

​

​

如您所见，顶部的记录有最高的last_modified_date_time，它在1的时候到达last_task_finish_seq，但是这个任务没有完成，因为它的task_finished is 0。因此，第一个记录应该是第二个记录，因为虽然它的last_modified_date_time较少，但它已经完成了。最后，由于它比其他已完成的任务具有更高的last_modified_date_time，所以它最终被完成。

我该怎么做呢？实际上，在将每组记录放在首位之后，我会使用一些类似的方法

`select * from (above query) where LAST_TASK_FINISH_SEQ = 1;`

因为我知道最后一次完成的任务在每个小组的顶端。

谢谢

Answer 1

要获得最近完成的行(或者最新的行，如果没有完成行和construction_type IN (1,2))，可以使用：

SELECT *
FROM   (
  SELECT *
  FROM   project_complete_data
  WHERE  project_id = 511
  AND    po_status IN (100, 101)
  AND    lot_status IN (102, 103)
  AND    task_status = 101
  AND    (  (   construction_type = 0
            AND task_finished = 1
            AND schedule_task_id IS NOT NULL)
         OR construction_type IN (1, 2)
         )
)
MATCH_RECOGNIZE (
  PARTITION BY custom_lot_no
  ORDER     BY task_finished DESC, task_last_modified_date DESC
  ALL ROWS PER MATCH
  PATTERN ( ^ latest_finished_row )
  DEFINE
    latest_finished_row AS 1 = 1
)

如果要对行进行排序而不进行筛选：

select proj_company_name, 
       custom_lot_no,
       task_name,
       task_actual_end_date,                           
       task_finish_date,
       task_finished,
       task_last_modified_date,
       to_char(task_last_modified_date, 'YYYY-MM-DD hh24:mi:ss')
         AS TASK_LAST_MODIFIED_DATE_TIME,
       ROW_NUMBER() OVER (
         PARTITION BY custom_lot_no
         ORDER BY task_finished DESC, task_last_modified_date desc
       ) AS LAST_TASK_FINISH_SEQ,
       po_total_amount,
       po_status,
       CASE 
       WHEN po_status = 101
       THEN po_total_amount   
       ELSE 0
       END as POs_COMPLETED,
       po_type,
       CASE 
       WHEN po_type = 100
       THEN po_total_amount 
       ELSE 0
       END as "COMMITTED"
FROM   project_complete_data
where  project_id = 511
AND    po_status  in (100, 101)
AND    lot_status in (102, 103)
And    task_status = 101
AND    (  (   construction_type = 0
          AND task_finished = 1 
          AND schedule_task_id is not null
          )    
       OR (   construction_type in (1, 2)
          AND task_finished in (1, 0)
          ) 
) 
order by custom_lot_no, task_last_modified_date desc;

db<>fiddle https://dbfiddle.uk/?rdbms=oracle_18&fiddle=f58462cae93d5440005767ed492f3379