时间戳

首页 > 解决方案 > 从 VARCHAR2 中提取最近的日期

sql - 从 VARCHAR2 中提取最近的日期

问题描述

数据：

USER_ID    VIOLATION_DATES
--------------------------------
1          18-Jul-21 > 24-Jul-21 
2          05-Aug-21
3          09-Jun-21
1          18-Jul-21

我有一个表格，其中包含用户及其违规日期的列。我想为每个用户提取最近的违规行为。

这是我写的查询：

select 
    USR_ID,    
    max(to_date(VIOLATION_DATES, 'DD-MON-YY')) as Most_Recent_VIOLATIONS
from 
    table
group by 
    USR_ID

但是我收到此错误：

ORA-01830: 日期格式图片在转换整个输入字符串之前结束

我认为这与最近一次违规的附加方式有关（18-Jul-21 > 24-Jul-21 ）。谁能提供有关如何为每个用户提取最近日期的任何说明？例如：

USER_ID    VIOLATION_DATES
--------------------------
1          24-Jul-21 
2          05-Aug-21
3          09-Jun-21

我知道这种存储方法并不理想，但这是我无法控制的。

标签： sqloracle

解决方案

您可以将多值字符串拆分为单独的行，然后正常“分组”（该with子句只是提供测试数据 - 替换您的实际表）：

with demo (user_id, violation_dates) as
     ( select 1, '18-Jul-21 > 24-Jul-21' from dual union all
       select 2, '05-Aug-21' from dual union all
       select 3, '09-Jun-21' from dual union all
       select 1, '18-Jul-21' from dual )
select user_id
     , max(to_date(regexp_substr(d.violation_dates, '[^> ]+', 1, r.rn) default null on conversion error,'DD-MON-YY')) as violation_date
from   demo d
       cross apply
             ( select rownum as rn
               from dual connect by rownum <= regexp_count(d.violation_dates,'>') +1 ) r
group by user_id
order by 1;

“转换错误时默认为空”需要 Oracle 12.2。

推荐阅读