时间戳

首页 > 解决方案 > 构建 SQL 查询以根据“状态”及其时间顺序查明堆栈中的记录

sql - 构建 SQL 查询以根据“状态”及其时间顺序查明堆栈中的记录

问题描述

我试图弄清楚如何构建一个查询，该查询将只返回 2 个事务记录，从 1 个堆栈到分组在一个序列号下的多个事务记录。我需要的两条记录是；

1) 堆栈中状态为“错误”的最新事务和 2) 该堆栈中状态为“成功”的下一个最新事务。

因此，一组示例记录可能如下所示......

1)  SERIAL_NUMBER   TRXN_ID     STATUS
    -------------   ---------   --------
1   08LKL47T8ZF6    16379796    Success
2   08LKL47T8ZF6    16380200    Error
3   08LKL47T8ZF6    16381077    Retired
4   08LKL47T8ZF6    16581500    Success
5   08LKL47T8ZF6    16581833    Success
6   08LKL47T8ZF6    16382800    Retired
7   08LKL47T8ZF6    16583505    Error
8   08LKL47T8ZF6    16586413    Error

到目前为止，我有以下内容......但这将返回上面列出的所有 8 条记录......

SELECT 
         STG.src_serial_number 
        ,STG.trxn_id
        ,STG.interface_status
  FROM 
       staging_table STG 

       -- All SN's where the most recent(max) transaction was an ERROR.
       ,(  SELECT X.src_serial_number
             FROM staging_table X  
                  ,(  SELECT MAXNUM.src_serial_number, max(MAXNUM.trxn_id) as trxn_id
                        FROM staging_table MAXNUM  
                       WHERE MAXNUM.src_serial_number = src_serial_number
                    GROUP BY MAXNUM.src_serial_number ) Y
            WHERE X.src_serial_number = Y.src_serial_number
              AND X.trxn_id = Y.trxn_id
              AND X.interface_status = 'Error'
        ) ERROR_REC

       -- No prior TRXN_ID / trxn for this Serial Number stack is a SUCCESS.
       ,(  SELECT PRIORSUCCESS.src_serial_number
             FROM staging_table PRIORSUCCESS
            WHERE PRIORSUCCESS.interface_status = 'Success' 
              AND PRIORSUCCESS.src_serial_number = src_serial_number
        ) SUCCESS_REC

 WHERE STG.src_serial_number = ERROR_REC.src_serial_number
   AND STG.src_serial_number = SUCCESS_REC.src_serial_number
   AND ERROR_REC.src_serial_number = SUCCESS_REC.src_serial_number

ORDER BY  STG.src_serial_number asc, STG.trxn_id asc

预期结果将是构建一个只返回记录#8（最新的错误）和#5（最新的成功）的查询

    SERIAL_NUMBER   TRXN_ID     STATUS
    -------------   ---------   --------
5   08LKL47T8ZF6    16581833    Success
8   08LKL47T8ZF6    16586413    Error

标签： sqloracle

解决方案

不太漂亮，但是 - 会有帮助吗？如果数据集很大，可能不会工作太快（请注意，从同一个表中获取 3 次！）。

SQL> with test (rn, serial, trxn_id, status) as
  2  (
  3  select 1,   '08LKL47T8ZF6',    16379796,    'Success' from dual union all
  4  select 2,   '08LKL47T8ZF6',    16380200,    'Error'   from dual union all
  5  select 3,   '08LKL47T8ZF6',    16381077,    'Retired' from dual union all
  6  select 4,   '08LKL47T8ZF6',    16581500,    'Success' from dual union all
  7  select 5,   '08LKL47T8ZF6',    16581833,    'Success' from dual union all
  8  select 6,   '08LKL47T8ZF6',    16382800,    'Retired' from dual union all
  9  select 7,   '08LKL47T8ZF6',    16583505,    'Error'   from dual union all
 10  select 8,   '08LKL47T8ZF6',    16586413,    'Error'   from dual
 11  )
 12  select * from test
 13  where trxn_id = (select max(trxn_id) from test
 14                   where status = 'Error'
 15                  )
 16     or trxn_id = (select max(trxn_id) from test
 17                   where status = 'Success'
 18                     and trxn_id < (select max(trxn_id) from test
 19                                    where status = 'Error'
 20                                   )
 21                  );

        RN SERIAL          TRXN_ID STATUS
---------- ------------ ---------- -------
         5 08LKL47T8ZF6   16581833 Success
         8 08LKL47T8ZF6   16586413 Error

SQL>

推荐阅读