关于时间里的小时\分的累积

[yxhdss]

我在数据库里定义的是字符型如:gg(Vchar2(10))它显示的值是00:00 ,可能有多条记录,如, 01:22、10:33\22:50\09:20 合计应该是34:05,不止如何处理,请求高见.

[hot.wind]

select sum(to_number(substr(gg,1,2))) + sum(to_number(substr(gg,4,2)))/60 from yourtab

[zealot_zk]

select to_char(sum(to_number(substr(gg,12)))+round(sum(to_number(substr(gg,42))/60))) || ':' || to_char(mod(sum(to_number(substr(gg,4,2))),60),'09') from dual;

我试过了没问题

[beckhambobo]

create or replace type mytabletype as table of number;
/

create or replace function strtab(p_str in varchar2)
return mytabletype
as
lstr varchar2(1000) default p_str||',';
ln number;
ldata mytabletype:=mytabletype();
begin
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
ldata.extend;
ldata(ldata.count):=ltrim(rtrim(substr(lstr,1,ln-1)));
lstr:=substr(lstr,ln+1);
end loop;
return ldata;
end;
/

SQL> select * from table(cast(strtab('11,12,13') as mytabletype));

COLUMN_VALUE
------------
11
12
13

SQL> create table bb(id varchar2(2),name varchar2(10));

Table created

SQL> insert into bb values('11','张三');

1 row inserted

SQL> insert into bb values('12','李四');

1 row inserted

SQL> insert into bb values('13','王五');

1 row inserted

SQL> select * from bb where id in (select * from table(cast(strtab('11,12,13') as mytabletype)));

ID NAME
-- ----------
11 张三
12 李四
13 王五

[beckhambobo]

再把记录值合计