select c-r as A,C-TRUNC(SYSDATE,'MONTH') AS B,trunc(SYSDATE)-R AS C FROM T我只需要一列值就可以了,第二个不对
select id, case when c is not null and to_char(r, 'yyyymm') = to_char(sysdate, 'yyyymm') then c - r when c is not null and to_char(r, 'yyyymm') <> to_char(sysdate, 'yyyymm') then c - trunc(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -1)) when c is null then trunc(sysdate) - r end from t
1 19.6388078703704 23 25.6388078703704 2015-1-4 AM 08:40:07 2015-1-24 2 3.29638888888889 3 29.2963888888889 2014-12-31 PM 04:53:12 2015-1-4 3 21.5571643518519 2015-1-8 AM 10:37:41 效果应该是这样 1 20 2015-1-4 AM 08:40:07 2015-1-24 2 4 2014-12-31 PM 04:53:12 2015-1-4 3 22 2015-1-8 AM 10:37:41
FROM T我只需要一列值就可以了,第二个不对
select id,
case
when c is not null and
to_char(r, 'yyyymm') = to_char(sysdate, 'yyyymm') then
c - r
when c is not null and
to_char(r, 'yyyymm') <> to_char(sysdate, 'yyyymm') then
c - trunc(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -1))
when c is null then
trunc(sysdate) - r
end
from t
2 3.29638888888889 3 29.2963888888889 2014-12-31 PM 04:53:12 2015-1-4
3 21.5571643518519 2015-1-8 AM 10:37:41 效果应该是这样
1 20 2015-1-4 AM 08:40:07 2015-1-24
2 4 2014-12-31 PM 04:53:12 2015-1-4
3 22 2015-1-8 AM 10:37:41
不要这句and to_char(ryrq, 'yyyymm') = to_char(sysdate, 'yyyymm')
才能够通用