比如下面这个sql里trunc((T2.C700020016-T2.C700020015)/60/60/24,0))用了好几次,我想给他取个别名,但是不能作为sql的查询结果显示出来
select decode(trunc((T2.C700020016-T2.C700020015)/60/60/24,0),0,'00',CASE WHEN length(trunc((T2.C700020016-T2.C700020015)/60/60/24,0)))>1 then trunc((T2.C700020016-T2.C700020015)/60/60/24,0)) else ('0' || trunc((T2.C700020016-T2.C700020015)/60/60/24,0))) end || '天' || decode(trunc(mod((T2.C700020016-T2.C700020015),60*60*24)/60/60,0),0,'00',trunc(mod((T2.C700020016-T2.C700020015),60*60*24)/60/60,0)) || '小时' || decode(trunc(mod((T2.C700020016-T2.C700020015),60*60)/60,0),0,'00',trunc(mod((T2.C700020016-T2.C700020015),60*60)/60,0)) || '分' from t153 t2
select decode(trunc((T2.C700020016-T2.C700020015)/60/60/24,0),0,'00',CASE WHEN length(trunc((T2.C700020016-T2.C700020015)/60/60/24,0)))>1 then trunc((T2.C700020016-T2.C700020015)/60/60/24,0)) else ('0' || trunc((T2.C700020016-T2.C700020015)/60/60/24,0))) end || '天' || decode(trunc(mod((T2.C700020016-T2.C700020015),60*60*24)/60/60,0),0,'00',trunc(mod((T2.C700020016-T2.C700020015),60*60*24)/60/60,0)) || '小时' || decode(trunc(mod((T2.C700020016-T2.C700020015),60*60)/60,0),0,'00',trunc(mod((T2.C700020016-T2.C700020015),60*60)/60,0)) || '分' from t153 t2
select decode(trunc((1343873132-1343706107)/60/60/24,0),0,'00',trunc((1343873132-1343706107)/60/60/24,0)) || '天' || decode(trunc(mod((1343873132-1343706107),60*60*24)/60/60,0),0,'00',trunc(mod((1343873132-1343706107),60*60*24)/60/60,0)) || '小时' || decode(trunc(mod((1343873132-1343706107),60*60)/60,0),0,'00',trunc(mod((1343873132-1343706107),60*60)/60,0)) || '分' from dual
select decode(a........) from (
select t2.*,trunc((T2.C700020016-T2.C700020015)/60/60/24,0) a,trunc(mod((T2.C700020016 - T2.C700020015), 60 * 60) / 60, 0) b from (
from t153 t2) )
(SELECT (1343873132 - 1343706107) x FROM dual)
SELECT lpad(trunc(x / 60 / 60 / 24), 2,0) || '天' ||
lpad(trunc(MOD(x, 60 * 60 * 24) / 60 / 60), 2,0) || '小时' ||
lpad(trunc(MOD(x, 60 * 60) / 60), 2,0) || '分'
FROM r
WITH r AS
(SELECT Numtodsinterval(1343873132 - 1343706107,'SECOND') x FROM dual)
SELECT lpad(EXTRACT(DAY FROM x),2,0)||'天'||
lpad(EXTRACT(HOUR FROM x),2,0)||'小时'||
lpad(EXTRACT(MINUTE FROM x),2,0)||'分'
FROM r