CASE
WHEN TRUNC(MONTHS_BETWEEN(sysdate,m.last_modified_time)/12) >=1
THEN TRUNC(MONTHS_BETWEEN(sysdate,m.last_modified_time)/12)|| '年前'
WHEN months_between(sysdate,m.last_modified_time) >=1
THEN months_between(sysdate,m.last_modified_time)|| '个月前'
WHEN ceil((sysdate - To_date(to_char(m.last_modified_time,'yyyy-mm-dd hh24-mi-ss') , 'yyyy-mm-dd hh24-mi-ss'))-1) >=1
THEN ceil((sysdate - To_date(to_char(m.last_modified_time,'yyyy-mm-dd hh24-mi-ss') , 'yyyy-mm-dd hh24-mi-ss'))-1)|| '天前'
WHEN ceil((sysdate - To_date(to_char(m.last_modified_time,'yyyy-mm-dd hh24-mi-ss') , 'yyyy-mm-dd hh24-mi-ss')) * 24 )-1 >=1
THEN ceil((sysdate - To_date(to_char(m.last_modified_time,'yyyy-mm-dd hh24-mi-ss') , 'yyyy-mm-dd hh24-mi-ss')) * 24 )-1 || '小时前'
WHEN ceil((sysdate - To_date(to_char(m.last_modified_time,'yyyy-mm-dd hh24-mi-ss') , 'yyyy-mm-dd hh24-mi-ss')) * 24 *60 )-1 >=1
THEN ceil((sysdate - To_date(to_char(m.last_modified_time,'yyyy-mm-dd hh24-mi-ss') , 'yyyy-mm-dd hh24-mi-ss')) * 24 *60 )-1 || '分钟前'
ELSE
'1分钟前'
END AS OPERATIONTIME
转载于:https://www.cnblogs.com/leifei/p/6235888.html