select sum(hh),sum(substr(mi,1,instr(mi,'分钟')-1)) from (select nvl(substr(you_col,1,instr(you_col,'小时')-1),0) hh, nvl(substr(you_col,instr(you_col,'时')+1),0) mi from you_table)
with a as (select '9小时20分钟' you_col from dual union all select '2小时20分钟' you_col from dual union all select '55分钟' you_col from dual)select floor(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60)||'小时'||(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60-floor(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60))*60 from (select nvl(substr(you_col,1,instr(you_col,'小时')-1),0) hh, nvl(substr(you_col,instr(you_col,'时')+1),0) mi from a) 12小时34.9999999999999999999999999999999999998
select floor(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60)||'小时'||(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60-floor(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60))*60 from (select nvl(substr(你表里的列,1,instr(你表里的列,'小时')-1),0) hh, nvl(substr(你表里的列,instr(你表里的列,'时')+1),0) mi from 你的表 )
SQL> select (summinutes - mod(summinutes,60)) / 60 || '小时' || mod(summinutes,60) || '分钟' from 2 ( 3 select sum(hour * 60 + minute) summinutes from 4 ( 5 select substr(col,1,instr(col,'H') -1 ) hour , 6 SUBSTR(COL,instr(col,'H') + 1 ,INSTR(COL,'M') - INSTR(COL,'H') -1) minute 7 from 8 ( 9 select replace(replace(col,'小时','H'),'分钟','M') col from 10 ( 11 ( 12 select '1小时20分钟' col from dual union all 13 select '1小时20分钟' from dual union all 14 select '1小时20分钟' from dual union all 15 select '1小时20分钟' from dual 16 ) 17 ) 18 ) 19 ) 20 ) 21 ;
(select nvl(substr(you_col,1,instr(you_col,'小时')-1),0) hh,
nvl(substr(you_col,instr(you_col,'时')+1),0) mi
from you_table)
with a as
(select '9小时20分钟' you_col from dual
union all
select '2小时20分钟' you_col from dual
union all
select '55分钟' you_col from dual)select floor(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60)||'小时'||(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60-floor(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60))*60 from
(select nvl(substr(you_col,1,instr(you_col,'小时')-1),0) hh,
nvl(substr(you_col,instr(you_col,'时')+1),0) mi
from a)
12小时34.9999999999999999999999999999999999998
(select nvl(substr(你表里的列,1,instr(你表里的列,'小时')-1),0) hh,
nvl(substr(你表里的列,instr(你表里的列,'时')+1),0) mi
from 你的表 )
2 (
3 select sum(hour * 60 + minute) summinutes from
4 (
5 select substr(col,1,instr(col,'H') -1 ) hour ,
6 SUBSTR(COL,instr(col,'H') + 1 ,INSTR(COL,'M') - INSTR(COL,'H') -1) minute
7 from
8 (
9 select replace(replace(col,'小时','H'),'分钟','M') col from
10 (
11 (
12 select '1小时20分钟' col from dual union all
13 select '1小时20分钟' from dual union all
14 select '1小时20分钟' from dual union all
15 select '1小时20分钟' from dual
16 )
17 )
18 )
19 )
20 )
21 ;
(SUMMINUTES-MOD(SUMMINUTES,60)
--------------------------------------------------------------------------------
5小时20分钟
select floor(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60)||'小时'||
round(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60-floor(sum(hh *60 + substr(mi,1,instr(mi,'分钟')-1))/60))*60) from
(select nvl(substr(你表里的列,1,instr(你表里的列,'小时')-1),0) hh,
nvl(substr(你表里的列,instr(你表里的列,'时')+1),0) mi
from 你的表 )