select to_char(ettime,'yyyy-mm-dd hh24:mi:ss')as et, to_char(sttime,'yyyy-mm-dd hh24:mi:ss')as st ,MAX(et-st) from telcdr这样不行呀,我是要一段日期,(条件还没有加)中的最大通话时间,怎么写???????????????????????
select max(t) from ( select ettime-sttime t from telcdr )
----先看看这个 select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual //时间差-年 select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual //时间差-月 select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual //时间差-天 select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual //时间差-时 select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual //时间差-分 select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒
select MAX(floor(to_number(etttime-stttime)*24*60*60)) as "最大通话时长(秒)" from telcdr where stttime between ... and ... ;
select trunc(m*24)||'小时'||trunc(mod(m*24,1)*60)||'分钟'|| trunc(mod(m*24*60,1)*60)||'秒' time1 from( select MAX(ettime-sttime)m from telcdr where...)
再给个:若持续时间为0小时n分n秒,小时不显示。若为0小时0分N秒,则只显示秒位 select case when m>=1/24 then trunc(m*24)||'小时' end ||case when m>=1/24/60 then trunc(mod(m*24,1)*60)||'分钟' end ||trunc(mod(m*24*60,1)*60)||'秒' time1 from( select MAX(ettime-sttime)m from telcdr where...)
---正解! 顶一个!select trunc(m*24)||'小时'||trunc(mod(m*24,1)*60)||'分钟'|| trunc(mod(m*24*60,1)*60)||'秒' time1 from ( select MAX(ettime-sttime) m from telcdr where etttime between ... and ... ) t
select trunc(m*24)||'小时'||trunc(mod(m*24,1)*60)||'分钟'|| trunc(mod(m*24*60,1)*60)||'秒' time1 from ( select MAX(ettime-sttime) m from telcdr where stttime between ... and ... ) t
这样可以。 select trunc(m*24)||'小时'||trunc(mod(m*24,1)*60)||'分钟'|| trunc(mod(m*24*60,1)*60)||'秒' time1 from ( select MAX(ettime-sttime) m from telcdr where etttime between ... and ... ) t
这样也可以。 select MAX(floor(to_number(etttime-stttime)*24*60*60)) from telcdr where stttime between ... and ...
oracle可以支持时间直接想减,不就是可以求出最大的通话时长了吗
这样可以吗? select to_char(ettime,'yyyy-mm-dd hh24:mi:ss')as et, to_char(sttime,'yyyy-mm-dd hh24:mi:ss')as st ,MAX(ettime-sttime) from telcdr group by(ettime,sttime);
select max(t) from
(
select ettime-sttime t from telcdr
)
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual //时间差-年
select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual //时间差-月
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual //时间差-天
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual //时间差-时
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual //时间差-分
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒
from telcdr
where stttime between ... and ... ;
trunc(mod(m*24*60,1)*60)||'秒' time1 from(
select MAX(ettime-sttime)m from telcdr where...)
select case when m>=1/24 then trunc(m*24)||'小时' end
||case when m>=1/24/60 then trunc(mod(m*24,1)*60)||'分钟' end
||trunc(mod(m*24*60,1)*60)||'秒' time1 from(
select MAX(ettime-sttime)m from telcdr where...)
---正解! 顶一个!select trunc(m*24)||'小时'||trunc(mod(m*24,1)*60)||'分钟'||
trunc(mod(m*24*60,1)*60)||'秒' time1 from (
select MAX(ettime-sttime) m from telcdr where etttime between ... and ... ) t
trunc(mod(m*24*60,1)*60)||'秒' time1 from (
select MAX(ettime-sttime) m from telcdr where stttime between ... and ... ) t
select trunc(m*24)||'小时'||trunc(mod(m*24,1)*60)||'分钟'||
trunc(mod(m*24*60,1)*60)||'秒' time1 from (
select MAX(ettime-sttime) m from telcdr where etttime between ... and ... ) t
select MAX(floor(to_number(etttime-stttime)*24*60*60))
from telcdr
where stttime between ... and ...
select to_char(ettime,'yyyy-mm-dd hh24:mi:ss')as et, to_char(sttime,'yyyy-mm-dd hh24:mi:ss')as st ,MAX(ettime-sttime) from telcdr group by(ettime,sttime);